with recursive cte as 
(select 
    share_id root_share_id,
    sharer_account_id root_account_id,
    0 depth,
    share_id cur_share_id,
    sharer_account_id cur_sharer_account_id,
    play_seconds,
    parent_share_id
from clip_share_events
where clip_id = 9001 
    and parent_share_id is null
    and date(share_time) >= '2025-07-01'
    and date(share_time) < '2025-07-08'
union all
select 
    cte.root_share_id root_share_id,
    cte.root_account_id root_account_id,
    cte.depth + 1 depth,
    cse.share_id cur_share_id,
    cse.sharer_account_id cur_sharer_account_id,
    cse.play_seconds play_seconds,
    cse.parent_share_id parent_share_id
from clip_share_events cse
right join cte on cse.parent_share_id = cte.cur_share_id
where cse.clip_id = 9001 
    and cse.parent_share_id is not null)
select 
    a.root_share_id root_share_id,
    pa.account_name root_account_name,
    a.total_share_count,
    a.max_depth max_depth,
    b.leaf_share_count leaf_share_count,
    a.distinct_account_count distinct_account_count,
    a.total_play_minutes total_play_minutes
from
(select 
    root_share_id,
    root_account_id,
    count(*) total_share_count,
    max(depth) max_depth,
 	-- 注意share_id和account_id的区别
    count(distinct cur_sharer_account_id) distinct_account_count,
    round(sum(play_seconds) / 60, 2) total_play_minutes
from cte
group by root_share_id, root_account_id) a
left join
(select 
    root_share_id, count(*) leaf_share_count
from cte t1
where not exists (
select 1
from cte t2
where t2.parent_share_id = t1.cur_share_id)
group by root_share_id) b
on a.root_share_id = b.root_share_id
left join podcast_accounts pa
on a.root_account_id = pa.account_id
order by max_depth desc, 
    distinct_account_count desc,
    total_share_count desc,
    root_share_id