-- =============================================
-- 查询名称: 发帖用户分层快照（近30天）
-- 创建时间: 2026-03-18
-- =============================================
WITH post_base AS (
  SELECT
    co.user_id,
    co.uuid,
    co.ctime,
    co.like_count,
    co.comment_count,
    co.has_video,
    co.is_interactive
  FROM collection co
  WHERE co.status = 'PUBLISHED'
    AND co.review_status = 'PASS'
    AND co.ctime >= DATE_SUB(NOW(), INTERVAL 30 DAY)
),
poster_profile AS (
  SELECT
    user_id,
    COUNT(*) AS posts_30d,
    AVG(like_count) AS avg_likes,
    AVG(comment_count) AS avg_comments,
    AVG(CASE WHEN has_video = 1 THEN 1 ELSE 0 END) AS video_ratio,
    AVG(CASE WHEN is_interactive = 1 THEN 1 ELSE 0 END) AS interactive_ratio
  FROM post_base
  GROUP BY user_id
)
SELECT
  CASE
    WHEN posts_30d >= 8 THEN 'S1_heavy_creator'
    WHEN posts_30d >= 3 THEN 'S2_mid_creator'
    ELSE 'S3_new_or_light_creator'
  END AS creator_segment,
  COUNT(*) AS user_cnt,
  ROUND(AVG(posts_30d), 2) AS avg_posts_30d,
  ROUND(AVG(avg_likes), 2) AS avg_like_per_post,
  ROUND(AVG(avg_comments), 2) AS avg_comment_per_post,
  ROUND(AVG(video_ratio) * 100, 2) AS avg_video_pct,
  ROUND(AVG(interactive_ratio) * 100, 2) AS avg_interactive_pct
FROM poster_profile
GROUP BY creator_segment
ORDER BY creator_segment;
