-- =============================================
-- 查询名称: 评论发布时间差分布（近30天）
-- 创建时间: 2026-03-18
-- =============================================
WITH base AS (
  SELECT
    TIMESTAMPDIFF(MINUTE, co.ctime, c.ctime) AS lag_minutes
  FROM comment c
  JOIN collection co
    ON co.uuid = c.root_entity_uuid
  WHERE c.status = 'PUBLISHED'
    AND c.review_status = 'NORMAL'
    AND c.parent_type = 'collection'
    AND c.level = 1
    AND c.llm_conversation_uuid IS NULL
    AND c.ctime >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    AND co.status = 'PUBLISHED'
    AND co.review_status = 'PASS'
)
SELECT
  CASE
    WHEN lag_minutes < 5 THEN '00_0-5m'
    WHEN lag_minutes < 15 THEN '01_5-15m'
    WHEN lag_minutes < 30 THEN '02_15-30m'
    WHEN lag_minutes < 60 THEN '03_30-60m'
    WHEN lag_minutes < 120 THEN '04_1-2h'
    WHEN lag_minutes < 360 THEN '05_2-6h'
    WHEN lag_minutes < 1440 THEN '06_6-24h'
    ELSE '07_24h+'
  END AS lag_bucket,
  COUNT(*) AS comment_cnt,
  ROUND(COUNT(*) / SUM(COUNT(*)) OVER () * 100, 2) AS pct
FROM base
WHERE lag_minutes >= 0
GROUP BY lag_bucket
ORDER BY lag_bucket;
