| Author |
 Topic  |
|
Cliff
Average Member
United States
501 Posts |
 Posted - 02 March 2006 : 12:29:46
|
I have a forum (not Snitz - sorry) that I would like to query some data out of.
I'd like to get a count of referrals for a time range for users.
Here is what I'd like:
Top 10 user names
Count of referrals
Date range (3/1/2006 - 5/31/2006)
Where user not Cliff (me) AND Post Count > 10
Sort by count of referral desc
Thanks |
https://squarewheelscycling.com/
https://www.pathlabtalk.com/ |
|
|
OneWayMule
Dev. Team Member & Support Moderator
Austria
4969 Posts |
|
|
Cliff
Average Member
United States
501 Posts |
Posted - 02 March 2006 : 13:58:27
|
I tried that. They are often very helpful, but no replies in the past few days.
The users here are always very helpful, so I thought I'd try here too.
As for the structure, it's all in one table, users. |
 |
|
|
OneWayMule
Dev. Team Member & Support Moderator
Austria
4969 Posts |
|
|
Cliff
Average Member
United States
501 Posts |
Posted - 02 March 2006 : 14:23:04
|
Thanks OWM, that kind of you.
Below is the whole structure for the table.
What I want to display is the name of the user "username" that referred people to join between 3/1/2006 and 5/31/2006. The current user who referred the new user is kept in the new user file as "referrerid".
I only want the top ten referrers, not the referees, and only from users that have at least 10 posts "posts".
I feel like the more I try and explain this the more confusing I make it. 
-- Table structure for table `vb_user`
--
CREATE TABLE `vb_user` (
`userid` int(10) unsigned NOT NULL auto_increment,
`usergroupid` smallint(5) unsigned NOT NULL default '0',
`membergroupids` varchar(250) NOT NULL default '',
`displaygroupid` smallint(5) unsigned NOT NULL default '0',
`username` varchar(100) NOT NULL default '',
`password` varchar(32) NOT NULL default '',
`passworddate` date NOT NULL default '0000-00-00',
`email` varchar(100) NOT NULL default '',
`styleid` smallint(5) unsigned NOT NULL default '0',
`parentemail` varchar(50) NOT NULL default '',
`homepage` varchar(100) NOT NULL default '',
`icq` varchar(20) NOT NULL default '',
`aim` varchar(20) NOT NULL default '',
`yahoo` varchar(32) NOT NULL default '',
`showvbcode` smallint(5) unsigned NOT NULL default '0',
`usertitle` varchar(250) NOT NULL default '',
`customtitle` smallint(6) NOT NULL default '0',
`joindate` int(10) unsigned NOT NULL default '0',
`daysprune` smallint(6) NOT NULL default '0',
`lastvisit` int(10) unsigned NOT NULL default '0',
`lastactivity` int(10) unsigned NOT NULL default '0',
`lastpost` int(10) unsigned NOT NULL default '0',
`posts` int(10) unsigned NOT NULL default '0',
`reputation` int(11) NOT NULL default '10',
`reputationlevelid` int(10) unsigned NOT NULL default '1',
`timezoneoffset` varchar(4) NOT NULL default '',
`pmpopup` smallint(6) NOT NULL default '0',
`avatarid` smallint(6) NOT NULL default '0',
`avatarrevision` int(10) unsigned NOT NULL default '0',
`options` int(10) unsigned NOT NULL default '15',
`birthday` varchar(10) NOT NULL default '',
`birthday_search` date NOT NULL default '0000-00-00',
`maxposts` smallint(6) NOT NULL default '-1',
`startofweek` smallint(6) NOT NULL default '1',
`ipaddress` varchar(15) NOT NULL default '',
`referrerid` int(10) unsigned NOT NULL default '0',
`languageid` smallint(5) unsigned NOT NULL default '0',
`msn` varchar(100) NOT NULL default '',
`emailstamp` int(10) unsigned NOT NULL default '0',
`threadedmode` smallint(5) unsigned NOT NULL default '0',
`autosubscribe` smallint(6) NOT NULL default '-1',
`pmtotal` smallint(5) unsigned NOT NULL default '0',
`pmunread` smallint(5) unsigned NOT NULL default '0',
`salt` char(3) NOT NULL default '',
`importuserid` bigint(20) NOT NULL default '0',
`vbchat_pref_font_face` varchar(255) NOT NULL default '',
`vbchat_pref_font_color` varchar(255) NOT NULL default '',
`vbchat_pref_user_ignore` text NOT NULL,
`vbchat_pref_status` varchar(10) NOT NULL default '',
`vbchat_pref_auto_on` tinyint(2) NOT NULL default '0',
`vbchat_pref_auto_msg` varchar(255) NOT NULL default '',
`vbchat_pref_in_room` int(15) NOT NULL default '0',
`profilepicrevision` int(10) unsigned NOT NULL default '0',
`showbirthday` smallint(5) unsigned NOT NULL default '2',
`invites` int(10) unsigned NOT NULL default '10',
`caninvite` tinyint(3) unsigned NOT NULL default '1',
`skype` varchar(32) NOT NULL default '',
PRIMARY KEY (`userid`),
KEY `usergroupid` (`usergroupid`),
KEY `username` (`username`),
KEY `birthday_search` (`birthday_search`),
KEY `importuser_index` (`importuserid`),
KEY `birthday` (`birthday`,`showbirthday`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1255 ;
|
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 02 March 2006 : 14:36:04
|
Try this ..
select * from vb_user where
username <> 'Cliff' and
posts > 10
and joindate ....
hmm on second thoughts you'll need to tell us the joindate format and a few other details.
Still better if the vb people help you  |
|
|
|
Cliff
Average Member
United States
501 Posts |
Posted - 02 March 2006 : 14:52:47
|
Thanks laser,
I got about that far, the joindate I can work out.
What I can't figure out is the who referred whom part.
Let's say Joe (userid 20) referred Mary, Bob and Mark. Mary, Bob and Mark will have Joe's userid (20) in their referrerid field.
So what I want to end up with is to show Joe referred 3 people.
As for vBulletin... Like I said above, they've been goon in the past, but not with custom stuff like this. |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 March 2006 : 04:36:55
|
It's a subquery before, but I really need help in doing them properly. It usually takes me 10-15 mins stuffing around until I know it's right.
Using Oracle the subquery part would be something like :
select count(*) from vb_user order by referrerid
Now's probably the time to ask which database you are using, the syntax may be different depending on the engine. |
|
|
|
Cliff
Average Member
United States
501 Posts |
Posted - 03 March 2006 : 06:53:57
|
| It's a MySQL db. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 March 2006 : 07:22:05
|
SELECT u1.username, (SELECT COUNT(*) FROM vb_user As u2. Group By referrerid WHERE u2.referrerid = u1.userid) As NumberOfReferredUsers
FROM vb_user as u1
This will get you the referrer count, but will only work with MySQL 4.0 or later. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
laser
Advanced Member
Australia
3859 Posts |
Posted - 03 March 2006 : 07:43:56
|
How about the entire thing ruirib, you can do it  |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 March 2006 : 10:36:55
|
SELECT * FROM (
SELECT u1.username, u1.posts, (SELECT COUNT(*) FROM vb_user As u2 WHERE u2.referrerid = u1.userid Group By referrerid ) As
NumberOfReferredUsers
FROM vb_user as u1
WHERE (u1.Joindate>='2006-01-03') AND (u1.Joindate<='2006-05-31') AND (u1.posts > 10) AND (u1.username<>'Cliff')
) As T
ORDER By T.NumberOfReferredUsers
LIMIT 0,10
I guess this should do it, though my experience with MySQL is not that big and I couldn't test this locally. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Cliff
Average Member
United States
501 Posts |
Posted - 03 March 2006 : 12:51:50
|
Rui,
Thank you very much, I'll put this on the page I want it on tonight and post a link here.
I really appreciate this. |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
Cliff
Average Member
United States
501 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 March 2006 : 21:23:36
|
Hope it's the obvious one:
SELECT * FROM (
SELECT u1.username, u1.posts, (SELECT COUNT(*) FROM vb_user As u2 WHERE u2.referrerid = u1.userid Group By u2.referrerid) As
NumberOfReferredUsers
FROM vb_user as u1
WHERE (u1.Joindate>='2006-01-03') AND (u1.Joindate<='2006-05-31') AND (u1.posts > 10) AND (u1.username<>'Cliff')
) As T
ORDER By T.NumberOfReferredUsers
LIMIT 0,10
Ok, try this one. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
Topic |
|
Topic Locked
