I need to get the TOP 5 referrers whos referrals spend the most money. I need to get:
1) the number of total referrals
2) the number of paying referrals only
3) the total amount those paying referrals have spent
So far this query returns the total paying referrals, but I need total referrals as well. Can it be done in 1 query or we need 2? Please help! Thank you!
SELECT t1.referrer as referrer,
count(t1.username) as total_referrals,
count(t2.username) as paying_referrals,
SUM(t2.payment) as total_payments
FROM USER_DATA t1, PAYMENT_INFO t2
WHERE t1.username = t2.username
GROUP BY referrer
ORDER BY total_payments DESC
LIMIT 0,5
USER_DATA (user data, about ~1000 records)
username referrer email password ...
user1 user67
user2 user89
user3 user89
user4 user57
user5 user57
user6 user89
user7 user67
user8 user89
...
PAYMENT_INFO (user payments, about ~300 records)
username payment
user1 100
user1 200
user2 350
user1 50
user5 500
user1 200
user3 150
user2 80
...