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

WITH RECURSIVE t1 AS(
    SELECT 
           share_id AS root_share_id,
           sharer_account_id AS root_account_id,
           share_id AS current_share_id,
           sharer_account_id AS current_account_id,
           0 AS depth,
           play_seconds
    FROM clip_share_events 
    WHERE parent_share_id IS NULL 
          AND clip_id=9001
          AND share_time BETWEEN '2025-07-01 00:00:00' AND '2025-07-07 23:59:59'

    UNION ALL

    SELECT t1.root_share_id,
           t1.root_account_id,
           c.share_id AS current_share_id,
           c.sharer_account_id AS current_account_id,
           t1.depth+1 AS depth,
           c.play_seconds
    FROM t1 
    JOIN clip_share_events c ON t1.current_share_id=c.parent_share_id
    WHERE c.clip_id = 9001
),
t2 AS (
    SELECT root_share_id,
           root_account_id,
           COUNT(current_share_id) AS total_share_count,
           MAX(depth) AS max_depth,
           SUM(CASE WHEN NOT EXISTS(
                SELECT 1
                FROM clip_share_events c2 
                WHERE  c2.parent_share_id=t1.current_share_id 
                       AND c2.clip_id = 9001
           ) THEN 1 ELSE 0 END) AS leaf_share_count,
           COUNT(DISTINCT current_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_id
)
SELECT t2.root_share_id,
       account_name AS root_account_name,
       total_share_count,
       max_depth,
       leaf_share_count,
       distinct_account_count,
       total_play_minutes
FROM t2 
JOIN podcast_accounts  p ON  t2.root_account_id=p.account_id
ORDER BY max_depth DESC,distinct_account_count DESC,total_share_count DESC,root_share_id ASC