I have 2 tables
TABLE USERS
USER_ID------USERNAME------------REGISTERTIME
1-----------------admin----------1432985224
2-----------------test ----------1433985224
3-----------------test2----------1332985224
TABLE REFERRALS
REFERRER_ID----------REFERRAL_ID
1-----------------------------2
1-----------------------------3
how to show with mysql this:
user admin have 2 referrals in last 30 days
user test have 0 referrals in last 30 days
user test2 have 0 referrals in last 30 days
Tried:
SELECT
users.username,
COUNT(referrals.user_id) AS referrals
FROM
users
LEFT JOIN
referrals ON
referrals.referral_id = users.user_id
WHERE users.registertime >= UNIX_TIMESTAMP( '2015-10-15' ) AND registertime < UNIX_TIMESTAMP( '2015-10-18' )
GROUP BY
users.user_id
What it shows is
only users that joined last 30 days and have referrals but i need it to show all users that HAVE NEW referrals in last 30 days
Wrong:(This shows only users that have joined in last 30 days and have referrals)
new user1 have 12 referrals new user2 have 3 referrals new user3 have 2 referrals
Correct: (this shows all users that have referrals that joined in last 30 days )
Old user1 have 52 referrals New user3 have 28 referrals Old user33 have 10 referrals
I hope i explained well
You need to bring in the information about the referrer and use that for filtering
SELECT u.username, COUNT(*) AS referrals
FROM users u JOIN
referrals r
ON r.referral_id = u.user_id JOIN
users ur
ON r.referrer_id = ur.user_id AND
ur.registertime >= UNIX_TIMESTAMP( '2015-10-15' ) AND
ur.registertime < UNIX_TIMESTAMP( '2015-10-18' )
GROUP BY u.username;
I don't think you need a LEFT JOIN for this. None of your examples have 0 counts. If you need them as well, then just change the joins in the query.
You should also get in the habit of using the same columns in the GROUP BY as you use in the SELECT. This is a good habit that can prevent future errors.
SELECT
users.username,
COUNT(referrals.referrer_id) AS referrals
FROM
users
LEFT JOIN
referrals ON
referrals.referrer_id = users.user_id
WHERE users.registertime >= UNIX_TIMESTAMP( '2015-10-15' ) AND registertime < UNIX_TIMESTAMP( '2015-10-18' )
GROUP BY
users.user_id