Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE public.users (
  id VARCHAR NOT NULL PRIMARY KEY,
  email VARCHAR,
  premium BOOLEAN DEFAULT FALSE
);

CREATE TABLE public.referrals (
  id VARCHAR NOT NULL REFERENCES public.users,
  referrer VARCHAR NOT NULL
)

CREATE TABLE



CREATE TABLE


INSERT INTO public.users 
	(id, email, premium)
VALUES
	('1', 'some@email.com', true),
	('2', 'another@email.com', false),
	('3', 'third@email.com', true),
	('4', 'fourth@email.com', true),
	('5', 'fifth@email.com', true),
	('6', 'sixth@email.com', false),
	('7', 'seventh@email.com', true)
;

INSERT INTO public.referrals 
	(id, referrer)
VALUES
        ('2', '1'),
        ('3', '1'),
        ('4', '1'),
        ('5', '2'),
        ('6', '2')
;

INSERT 0 7



INSERT 0 5


TABLE public.users;


id
email
premium


1
some@email.com
t

2
another@email.com
f

3
third@email.com
t

4
fourth@email.com
t

5
fifth@email.com
t

6
sixth@email.com
f

7
seventh@email.com
t



SELECT 7


TABLE  public.referrals;


id
referrer


2
1

3
1

4
1

5
2

6
2



SELECT 5


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


id
email
premium
referred


1
some@email.com
t
2

2
another@email.com
f
1

3
third@email.com
t
0

4
fourth@email.com
t
0

5
fifth@email.com
t
0

6
sixth@email.com
f
0

7
seventh@email.com
t
0



SELECT 7

id	email	premium
1	some@email.com	t
2	another@email.com	f
3	third@email.com	t
4	fourth@email.com	t
5	fifth@email.com	t
6	sixth@email.com	f
7	seventh@email.com	t