8

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;
Santeri Sarle
  • 863
  • 7
  • 12

1 Answers1

11

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

sqlfiddle

D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • This filters based on the referrer's premium status. I would like to filter the referred users based on their premium status. So the query should return id 1 having referred two users, and id 2 having referred one user. – Santeri Sarle May 02 '22 at 09:00