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

WITH RECURSIVE tmp AS(
    -- 找到根节点
    SELECT cs.share_id root_share_id, account_name root_account_name,
        cs.share_id cur_share_id,
        cs.sharer_account_id cur_share_account_id,
        0 depth,
        play_seconds
    FROM podcast_accounts pa 
    JOIN clip_share_events cs ON cs.sharer_account_id = pa.account_id
    WHERE parent_share_id IS NULL AND DATE(	share_time) BETWEEN '2025-07-01' AND '2025-07-07'
        AND clip_id = '9001'
    -- 开始根据根节点的分支迭代
    UNION ALL
    SELECT root_share_id, root_account_name,
        cs.share_id cur_share_id,
        cs.sharer_account_id cur_share_account_id,
        depth + 1,
        cs.play_seconds
    FROM tmp t  
    JOIN clip_share_events cs ON cs.parent_share_id= t.cur_share_id
    WHERE clip_id ='9001'
)


SELECT root_share_id, root_account_name, COUNT(cur_share_id) total_share_count,
    MAX(depth) max_depth,
    SUM(CASE WHEN NOT EXISTS (SELECT * FROM clip_share_events cs 
        WHERE tmp.cur_share_id = cs.parent_share_id	) THEN 1 ELSE 0 END) leaf_share_count,
    COUNT(DISTINCT cur_share_account_id) distinct_account_count,
    ROUND(SUM(play_seconds)/60,2) total_play_minutes
FROM tmp 
GROUP BY 1,2 
ORDER BY 4 DESC, 6 DESC, 3 DESC, 1