SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    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!!
    Convert your dollars into silver coins. www.convert2silver.com

  2. #2
    SitePoint Zealot Zurev's Avatar
    Join Date
    Feb 2009
    Posts
    171
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you be more specific as to how it "messes up the data"?

  3. #3
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    Convert your dollars into silver coins. www.convert2silver.com

  4. #4
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,066
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Why not simply add a column total to your users table. Every time when someone comes from a referral you update the total column:
    Code MySQL:
    UPDATE users
    SET
    total = total + 1
    WHERE id = .........
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  5. #5
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    That would work going forward, but not for the many accounts that are already in the DB.

    Any other ideas?
    Convert your dollars into silver coins. www.convert2silver.com

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT sponsor
         , firstname
         , lastname
         , COUNT(*) AS total 
      FROM users 
    GROUP 
        BY sponsor 
         , firstname
         , lastname
    ORDER 
        BY total DESC;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!
    Convert your dollars into silver coins. www.convert2silver.com

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    And where do you store the sponsor's first and last name? In another table? In the user table?

  9. #9
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Yes, the sponsor's firstname, lastname is stored in the users table.
    Convert your dollars into silver coins. www.convert2silver.com

  10. #10
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    What are the column names in your users table?

  11. #11
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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.
    Convert your dollars into silver coins. www.convert2silver.com

  12. #12
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    try

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

  13. #13
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Hmmm, this didn't produce the right results either. Can I post a screenshot to this thread?
    Convert your dollars into silver coins. www.convert2silver.com

  14. #14
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    A screenshot of a relevent sample of your data would definitely help.

  15. #15
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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?
    Convert your dollars into silver coins. www.convert2silver.com

  16. #16
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    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!


    Convert your dollars into silver coins. www.convert2silver.com

  17. #17
    Non-Member
    Join Date
    Dec 2010
    Location
    /home/pc
    Posts
    186
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    i woulda posted this sooner, to avoid all the unsuccessful attempts, but i was out for a few hours...
    Code:
    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;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Guru
    Join Date
    Sep 2004
    Location
    Provo, UT
    Posts
    858
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    You freak'n rock Rudy! Are you the King of correlating data or what?
    Convert your dollars into silver coins. www.convert2silver.com


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •