Mini-challenge M03 — top 3 closer mỗi tháng + completion rate theo tuần

-- Stage 1: aggregate per user per month
SELECT
  date_trunc('month', updated_at)                              AS month,
  assignee_id,
  COUNT(*)                                                     AS done_count,
  AVG(EXTRACT(DAY FROM (updated_at - created_at)))             AS avg_days
FROM tasks
WHERE status = 'done'
  AND updated_at >= now() - INTERVAL '6 months'
  AND assignee_id IS NOT NULL
GROUP BY 1, 2;

-- Stage 2: wrap CTE, add rank
WITH monthly AS (
  SELECT
    date_trunc('month', updated_at)                            AS month,
    assignee_id,
    COUNT(*)                                                   AS done_count,
    AVG(EXTRACT(DAY FROM (updated_at - created_at)))           AS avg_days
  FROM tasks
  WHERE status = 'done'
    AND updated_at >= now() - INTERVAL '6 months'
    AND assignee_id IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  month,
  assignee_id,
  done_count,
  ROUND(avg_days::numeric, 1)                                  AS avg_complete_days,
  DENSE_RANK() OVER (PARTITION BY month ORDER BY done_count DESC) AS rank
FROM monthly;

-- Stage 3: filter top 3 + JOIN users
WITH monthly AS (
  SELECT
    date_trunc('month', updated_at)                            AS month,
    assignee_id,
    COUNT(*)                                                   AS done_count,
    AVG(EXTRACT(DAY FROM (updated_at - created_at)))           AS avg_days
  FROM tasks
  WHERE status = 'done'
    AND updated_at >= now() - INTERVAL '6 months'
    AND assignee_id IS NOT NULL
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    month,
    assignee_id,
    done_count,
    ROUND(avg_days::numeric, 1)                                AS avg_complete_days,
    DENSE_RANK() OVER (PARTITION BY month ORDER BY done_count DESC) AS rank
  FROM monthly
)
SELECT
  r.month,
  r.rank,
  u.name,
  r.done_count,
  r.avg_complete_days
FROM ranked r
INNER JOIN users u ON r.assignee_id = u.id
WHERE r.rank <= 3
ORDER BY r.month DESC, r.rank;

-- Per ISO week: total task vs done vs ratio
SELECT
  date_trunc('week', created_at)                               AS week,
  COUNT(*)                                                     AS total,
  COUNT(*) FILTER (WHERE status = 'done')                      AS done,
  ROUND(
    COUNT(*) FILTER (WHERE status = 'done') * 100.0
      / NULLIF(COUNT(*), 0),
    2
  )                                                            AS completion_pct
FROM tasks
WHERE created_at >= now() - INTERVAL '12 weeks'
GROUP BY 1
ORDER BY week DESC;

-- Cast 2 query ve common schema (metric_type, period, rank, subject, metric_value, extra)
SELECT
  'monthly_top'                                                AS metric_type,
  to_char(month, 'YYYY-MM')                                   AS period,
  rank::int                                                    AS rank,
  name                                                         AS subject,
  done_count                                                   AS metric_value,
  avg_complete_days::text                                      AS extra
FROM (
  -- ...Stage 3 ranked top 3 query...
  WITH monthly AS (
    SELECT
      date_trunc('month', updated_at)                          AS month,
      assignee_id,
      COUNT(*)                                                 AS done_count,
      AVG(EXTRACT(DAY FROM (updated_at - created_at)))         AS avg_days
    FROM tasks
    WHERE status = 'done'
      AND updated_at >= now() - INTERVAL '6 months'
      AND assignee_id IS NOT NULL
    GROUP BY 1, 2
  ),
  ranked AS (
    SELECT
      month, assignee_id, done_count,
      ROUND(avg_days::numeric, 1)                              AS avg_complete_days,
      DENSE_RANK() OVER (PARTITION BY month ORDER BY done_count DESC) AS rank
    FROM monthly
  )
  SELECT r.month, r.rank, u.name, r.done_count, r.avg_complete_days
  FROM ranked r
  INNER JOIN users u ON r.assignee_id = u.id
  WHERE r.rank <= 3
) q1

UNION ALL

SELECT
  'weekly_completion'                                          AS metric_type,
  to_char(week, 'YYYY-WW')                                    AS period,
  NULL::int                                                    AS rank,
  NULL                                                         AS subject,
  total                                                        AS metric_value,
  completion_pct::text                                         AS extra
FROM (
  SELECT
    date_trunc('week', created_at)                             AS week,
    COUNT(*)                                                   AS total,
    COUNT(*) FILTER (WHERE status = 'done')                    AS done,
    ROUND(
      COUNT(*) FILTER (WHERE status = 'done') * 100.0
        / NULLIF(COUNT(*), 0),
      2
    )                                                          AS completion_pct
  FROM tasks
  WHERE created_at >= now() - INTERVAL '12 weeks'
  GROUP BY 1
) q2

ORDER BY metric_type, period DESC, rank;

-- Seed 100k task de benchmark
INSERT INTO tasks (project_id, assignee_id, title, status, created_at, updated_at)
SELECT
  (random() * 5 + 1)::int,
  (random() * 10 + 1)::int,
  'Task ' || generate_series,
  CASE WHEN random() < 0.4 THEN 'done' ELSE 'todo' END,
  now() - (random() * 180) * INTERVAL '1 day',
  now() - (random() * 30) * INTERVAL '1 day'
FROM generate_series(1, 100000);

-- Analyze Query 1
\timing on
EXPLAIN (ANALYZE, BUFFERS)
WITH monthly AS (
  SELECT
    date_trunc('month', updated_at)                            AS month,
    assignee_id,
    COUNT(*)                                                   AS done_count,
    AVG(EXTRACT(DAY FROM (updated_at - created_at)))           AS avg_days
  FROM tasks
  WHERE status = 'done'
    AND updated_at >= now() - INTERVAL '6 months'
    AND assignee_id IS NOT NULL
  GROUP BY 1, 2
),
ranked AS (
  SELECT month, assignee_id, done_count,
         ROUND(avg_days::numeric, 1)                           AS avg_complete_days,
         DENSE_RANK() OVER (PARTITION BY month ORDER BY done_count DESC) AS rank
  FROM monthly
)
SELECT r.month, r.rank, u.name, r.done_count, r.avg_complete_days
FROM ranked r
INNER JOIN users u ON r.assignee_id = u.id
WHERE r.rank <= 3
ORDER BY r.month DESC, r.rank;
-- Plan: HashAggregate -> WindowAgg -> Sort -> Hash Join -> Filter
-- Estimated: ~150ms tren 100k task, sequential scan

-- BUG 1: WHERE rank cung level voi window function
SELECT
  assignee_id,
  DENSE_RANK() OVER (PARTITION BY month ORDER BY done_count DESC) AS rank
FROM monthly
WHERE rank <= 3;
-- ERROR: column "rank" does not exist
-- Fix: wrap CTE hoac subquery nhu Stage 3 o tren

-- BUG 2: integer division mat decimal
COUNT(*) FILTER (WHERE status = 'done') / COUNT(*)
-- int / int = int: 45 / 100 = 0, khong phai 0.45
-- Fix A: nhan 100.0 truoc khi chia
COUNT(*) FILTER (WHERE status = 'done') * 100.0 / NULLIF(COUNT(*), 0)
-- Fix B: cast explicit
COUNT(*) FILTER (WHERE status = 'done')::numeric * 100 / NULLIF(COUNT(*), 0)

-- BUG 3: date_trunc khong nhat quan timezone
date_trunc('week', created_at)
-- Truncate theo session timezone -- khac nhau giua servers
-- Fix: explicit AT TIME ZONE truoc khi truncate
date_trunc('week', created_at AT TIME ZONE 'UTC')

-- BUG 4: avg_days NULL khi khong co row done
AVG(EXTRACT(DAY FROM (updated_at - created_at)))
-- Neu khong co row nao match WHERE status = 'done', AVG tra ve NULL
-- Fix defensive:
COALESCE(AVG(EXTRACT(DAY FROM (updated_at - created_at))), 0)