I have the following query which gives me the amount of referrals users have. However, I would like to only count the referral if the referred user has activated the premium plan. How could this be achieved?
Dbfiddle here.
I am using PostgreSQL version 14.
SELECT users.id, users.email, users.premium,
COUNT(referrals.referrer) as referred
FROM users
LEFT JOIN referrals
ON users.id = referrals.referrer
GROUP BY users.id
ORDER BY referred DESC;
You can try to use FILTER clause or condition aggregate function.
SELECT users.id, users.email, users.premium,
COUNT(referrals.referrer) FILTER(WHERE premium = true) as referred
FROM users
LEFT JOIN referrals
ON users.id = referrals.referrer
GROUP BY users.id, users.email, users.premium
ORDER BY referred DESC;
Edit
From you comment you I think you can try to use subquery to do aggregate function to find premium user first, then do OUTER JOIN for user table.
SELECT u.*,
coalesce(referred,0) referred
FROM users u
LEFT JOIN(
SELECT f.referrer,
COUNT(f.id) FILTER(WHERE u1.premium = true) as referred
FROM users u1
JOIN referrals f
ON u1.id = f.id
GROUP BY f.referrer
) t1
ON t1.referrer = u.id
