A query to find the most popular sponsors

My website users are given their own referral address they can put on their website. I am trying to determine which of my users bring the most number of new signups. Here is the query I came up with:

select sponsor, count(sponsor) as total from users group by sponsor order by total desc;

But when I try to throw in firstname and lastname into the query, to find out the names of those who refer the most, it screws up the data. How can the above query be tweaked so that it provides an additional column of firstname lastname?

Thanks!!

Could you be more specific as to how it “messes up the data”?

As soon as I add columns for firstname, lastname, suddenly the sponsor number for that person is no longer correct. I’m guessing I need help with the group by or order by to fix this. Is there an easy way to paste in the results and still have it maintain the columns?

Thanks!

Why not simply add a column total to your users table. Every time when someone comes from a referral you update the total column:


UPDATE users
SET
total = total + 1
WHERE id = ......... 

That would work going forward, but not for the many accounts that are already in the DB.

Any other ideas?

SELECT sponsor
     , firstname
     , lastname
     , COUNT(*) AS total 
  FROM users 
GROUP 
    BY sponsor 
     , firstname
     , lastname
ORDER 
    BY total DESC;

Rudy, this one didn’t work as I was hoping. I mean it produced results without an error, but the data isn’t grouping right or something. The following query gets the closet to what I’m trying to achieve:

select sponsor, count(sponsor) as total from users group by sponsor order by total desc;

Distributors put their referral link on facebook and other places around the net. When people signup using that link, a new row is created in the users table. The sponsor column will be populated with the uID of the distributor who posted his link out on the web.

So the query above creates two columns: sponsor and total. Its counting how many people have signed up under each uID. The results from the query are correct, except I just can’t figure out how to also get the name corresponding with each particular sponsor ID. Does this help you see what I’m trying to do?

Thanks!

And where do you store the sponsor’s first and last name? In another table? In the user table?

Yes, the sponsor’s firstname, lastname is stored in the users table.

What are the column names in your users table?

The relevant ones are date, uID, firstname, lastname, sponsor. If a person comes straight to our website to signup, then they will have nothing in their sponsor field. If, however, they come via a referral link, then they will have that distributor’s uID in their sponsor field. I hope this helps.

try

SELECT DISTINCT sponsor, firstName, lastName, COUNT(*)
FROM users
GROUP BY sponsor
ORDER BY COUNT(*), lastName, firstName DESC

Hmmm, this didn’t produce the right results either. Can I post a screenshot to this thread?

A screenshot of a relevent sample of your data would definitely help.

I wonder if part of my problem is that some users have sponsor IDs that point to another user’s uID. Then some users have sponsor IDs that are the referring distributor’s Young Living Essential Oils distributor number.

See, a few years ago I changed the site to support both types of IDs when creating their referral address. It could be the internal uID that we associate with their account, or they could use their YL distributor number. So some of the sponsor IDs that this query is trying to group by does NOT associate with another user’s uID.

Ugh. What am I going to do?

Here is my original query:

select sponsor, count(sponsor) as total from users group by sponsor order by total desc;

In the image below you will see that sponsor 0 has 9525. This means there are 9525 that signed up on the website without using a referral address.

The next user, 110, has had 1874 users signup because of his referral address, etc. Out of all the queries suggested on this thread, this is the only one that at least produces the correct total for each sponsor.

Now if we can somehow add the firstname, lastname so we can easily see what user is tied to uID 110, 121, 305, etc.

Thanks!

I wonder if part of my problem is that some users have sponsor IDs that point to another user’s uID. Then some users have sponsor IDs that are the referring distributor’s Young Living Essential Oils distributor number.

If the posted solutions don’t give the required results then I think there is something wrong or corrupted in your users table data.

Can you post a relevent sample of your users table data showing the different types of uID’s in your sponsor field and how they should relate to the actual data in your uID field.

i woulda posted this sooner, to avoid all the unsuccessful attempts, but i was out for a few hours…

SELECT users.sponsor
     , sponsors.firstname
     , sponsors.lastname
     , COUNT(*) as total 
  FROM users 
INNER
  JOIN users as sponsors
    ON sponsers.uid = users.sponsor
GROUP 
    BY sponsor 
ORDER 
    BY total DESC;

:slight_smile:

You freak’n rock Rudy! Are you the King of correlating data or what?