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

with recursive t1 as (
    select
        c.share_id as root_share_id,
        p.account_name as root_account_name,
        c.share_id as id,
        c.sharer_account_id,
        0 as depth,
        c.play_seconds as play_seconds
    from
        podcast_accounts p
    join
        clip_share_events c on c.sharer_account_id = p.account_id
    where
        c.clip_id = 9001 and c.parent_share_id is NULL  and c.share_time >= '2025-07-01' and c.share_time <'2025-07-08'

    union all
    
    select
        t.root_share_id,
        t.root_account_name,
        c.share_id as id,
        c.sharer_account_id ,
        t.depth + 1 as depth,
        c.play_seconds as play_seconds
    from
        t1 t
    join
        clip_share_events c on c.parent_share_id = t.id
)

select
    root_share_id,
    root_account_name,
    count(*) as total_share_count,
    max(depth) as max_depth,
    sum(case 
    when id not in (select parent_share_id from clip_share_events where parent_share_id is not null)
    then 1 else 0 
end) as leaf_share_count,
    count(distinct sharer_account_id) as distinct_account_count,
    round(sum(play_seconds) / 60,2) as total_play_minutes
from
    t1
group by
    root_share_id,
    root_account_name
order by
    max_depth desc , distinct_account_count desc ,total_share_count desc ,root_share_id