题解 | 播客精彩片段裂变传播链统计
播客精彩片段裂变传播链统计
https://www.nowcoder.com/practice/c67a5e17dd474032aa5eac5dcffca317
with recursive t1 as(
select
share_id,
0 as fsum_share_id,# 父节点
c.play_seconds,
c.sharer_account_id,
share_id as root_share_id,
account_name as root_account_name,
0 as max_depth
from clip_share_events c
join podcast_accounts p on c.sharer_account_id=p.account_id
where parent_share_id is null and c.clip_id = 9001 and
share_time between '2025-07-01 00:00:00' and '2025-07-07 23:59:59'
union all
select
c1.share_id,
fsum_share_id+1 as fsum_share_id,
c1.play_seconds,
c1.sharer_account_id,
root_share_id,
root_account_name,
max_depth+1 as max_depth
from t1 join clip_share_events c1 on t1.share_id=c1.parent_share_id
where t1.max_depth is not null )
#若某个节点在本传播链内没有任何子节点，则它是叶子节点。 所有去重节点-所有做过父的节点
select
root_share_id,
root_account_name,
count(*) as total_share_count,
max(max_depth) as max_depth,
count(distinct share_id)-max(fsum_share_id) as leaf_share_count,
count(distinct sharer_account_id) as distinct_account_count,
round(sum(play_seconds)/60,2) as total_play_minutes
from(select * from t1) t
group by root_share_id,root_account_name
order by max_depth desc,distinct_account_count desc,
total_share_count desc,root_share_id
