SQL referrals view

Question

I have a table with users:

id              referred_by_user_id
-----------------------------------
1               null
2               1
3               1
4               2  

I need to write request to get number of people referred by user in two levels.

• First - direct referral (example: user 1 referred users 2 3. count for level 1 = 2)
• Second - user 1 referred to users 2 and 3, user 2 referred to user 4. So count for level 2 should be 1

Result of query should be:

id              referred_user_tier_one_total      referred_user_tier_two_total
------------------------------------------------------------------------------
1               2                                 1 
2               1                                 null
3               null                              null 
4               null                              null

I figured out how to count referred_user_tier_one_total:

select 
    "id", referred_user_tier_one_total
from 
    "user"
inner join 
    (select 
         count(*) as referred_user_tier_one_total, referred_by_user_id
     from 
         "user"
     where 
         "user".referred_by_user_id is not null
     group by 
         "user".referred_by_user_id) ur on "user".id = ur.referred_by_user_id

But I don't understand how to calculate referred_user_tier_two_total. Please, help

UPD:

Thanks @Stoff for SQL Server solution.

Here is the script rewritten for Postgres

WITH RECURSIVE agg AS 
(
    SELECT
        a.ID, a.referred_by_user_id, 
        COUNT(b.referred_by_user_id) AS "count"
    FROM
        "user" a
    LEFT JOIN
        "user" b ON a.ID = b.referred_by_user_id
    GROUP BY 
        a.ID, a.referred_by_user_id
)
SELECT 
    a.ID, 
    a.Count AS referred_user_tier_one_total, 
    CASE 
        WHEN SUM(b.count) IS NULL 
            THEN 0 
            ELSE SUM(b.count) 
    END AS referred_user_tier_two_total 
FROM
    agg a
LEFT JOIN
    agg b ON a.ID = b.referred_by_user_id
GROUP BY
    a.ID, a.Count
ORDER BY 
    a.ID

Answer 1

Here is a solution which works in Postgres.
We could carry on writing more levels in the same way.

create table referals(
id int,
referred_by_user_id int);
insert into referals values
(1 ,         null),
(2 ,            1),
(3 ,            1),
(4 ,            2 );

select
t0.id,
count(t1.id) tier1,
count(t2.id) tier2
from
referals t0
left join referals t1
on t0.id = t1.referred_by_user_id
left join referals t2
on t1.id = t2.referred_by_user_id
group by t0.id

id | tier1 | tier2
-: | ----: | ----:
 1 |     2 |     1
 2 |     1 |     0
 3 |     0 |     0
 4 |     0 |     0

db<>fiddle here