题解 | 播客精彩片段裂变传播链统计

with recursive temp as (
    select 
        c1.share_id as root_share_id,
        account_name as root_account_name,
        c1.share_id as cur_share_id,
        c1.sharer_account_id as cur_id,
        0 as depth,
        play_seconds
    from podcast_accounts p 
        join clip_share_events c1 on p.account_id=c1.sharer_account_id
    where c1.clip_id = 9001 and c1.parent_share_id is null
        and date(c1.share_time) between '2025-07-01' and '2025-07-07'

    union all

    select 
        root_share_id ,
        root_account_name ,
        c1.share_id as cur_share_id,
        c1.sharer_account_id as cur_id,
        depth+1,
        c1.play_seconds
    from temp p 
        join clip_share_events c1 on p.cur_share_id=c1.parent_share_id
    where c1.clip_id = 9001 
)
    select 
        root_share_id,
        root_account_name,
        count(cur_share_id) as total_share_count,
        max(depth) as max_depth,
        sum(case when not exists 
            (select 1 
                from clip_share_events c1 
                where c1.parent_share_id =temp.cur_share_id and c1.clip_id = 9001) then 1 else 0 end) as leaf_share_count,
        count(distinct cur_id) as distinct_account_count,
        round(sum(play_seconds)/60,2) as total_play_minutes 
    from temp
    group by root_share_id,root_account_name
    order by max_depth desc,distinct_account_count desc,total_share_count desc,root_share_id;