How to count recursively the number of referrals?

Question

MySQL

Hello!

Mysql 8

The task is this: to count the number of not all referrals, but the number of referrals from the user at each level, up to level 3.

СREATE TABLE tb_users (
id int not null unsigned primary key,
username varchar(20),
id_referer int unsigned
);

CREATE INDEX fk_tree_tree ON tb_users (id_referer);

ALTER TABLE al_tree ADD CONSTRAINT fk_tree_tree
    FOREIGN KEY (id_referer) REFERENCES tb_users (id) ON UPDATE CASCADE ON DELETE CASCADE;

I made this request

with recursive cte (id, id_referer, lvl) as (
  select     id,
             id_referer,
         1 lvl
  from       tb_users
  where      id_referer = 1
  union DISTINCT
  select     p.id,
             p.id_referer,
         lvl + 1
  from       tb_users p
  inner join cte
          on p.id_referer = cte.id
  where lvl < 3
)
select * from cte

Now I get the following output

id id_referer lvl
2 1 1
3 1 1
.......

I need to get the following response

id amount_ref_lvl_1 amount_ref_lvl_2 amount_ref_lvl_3
1 333 400 5999

That is, just display the number of referrals from the user at each level.

At the end I change toselect * from cte

select COUNT(CASE WHEN `lvl` = 1 THEN 1 END) `lvl_1`, COUNT(CASE WHEN `lvl` = 2 THEN 1 END) `lvl_2`, COUNT(CASE WHEN `lvl` = 3 THEN 1 END) `lvl_3` from cte

Am I doing it right? Or is there something simpler and better?

Answer 1

I would write like this:

with recursive cte (main_id, id, id_referer, lvl) as (
  select id,    
   id,
             id_referer,
         1 lvl
  from       tb_users
  where      id_referer is null
  union all
  select  main_id,   
  p.id,
             p.id_referer,
         lvl + 1
  from       tb_users p
  inner join cte
          on p.id_referer = cte.id
  where lvl < 3
)
select main_id, 
       count(case lvl when 1 then 1 end) cnt_lvl1,
       count(case lvl when 2 then 1 end) cnt_lvl2,
       count(case lvl when 3 then 1 end) cnt_lvl3
  from cte
 group by main_id

Pay attention to id_referer is null as an entry point and the first level
main_id - as the first level id
Well, in general, you will understand, I hope ;-)