Mini-challenge M03 — top closer tháng + weekly completion

-- 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 >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH
  -- date arithmetic dialect: PG: now()-INTERVAL '6 months'; MySQL: DATE_SUB(NOW(),INTERVAL 6 MONTH)
  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 >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH
    AND assignee_id IS NOT NULL
  GROUP BY 1, 2
)
SELECT
  month,
  assignee_id,
  done_count,
  ROUND(CAST(avg_days AS NUMERIC), 1)                          AS avg_complete_days,
  -- PG shorthand: avg_days::numeric -- MySQL/SQL Server: CAST(avg_days AS DECIMAL(10,2))
  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 >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH
    AND assignee_id IS NOT NULL
  GROUP BY 1, 2
),
ranked AS (
  SELECT
    month,
    assignee_id,
    done_count,
    ROUND(CAST(avg_days AS 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 >= CURRENT_TIMESTAMP - INTERVAL '12' WEEK
  -- PG: now() - INTERVAL '12 weeks'; MySQL: DATE_SUB(NOW(), INTERVAL 12 WEEK)
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,
  -- to_char: PG/Oracle. MySQL: DATE_FORMAT(month,'%Y-%m'). SQL Server: FORMAT(month,'yyyy-MM')
  CAST(rank AS INT)                                            AS rank,
  name                                                         AS subject,
  done_count                                                   AS metric_value,
  CAST(avg_complete_days AS CHAR)                              AS extra
  -- PG shorthand: rank::int, avg_complete_days::text
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 >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH
      AND assignee_id IS NOT NULL
    GROUP BY 1, 2
  ),
  ranked AS (
    SELECT
      month, assignee_id, done_count,
      ROUND(CAST(avg_days AS 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,
  CAST(NULL AS INT)                                            AS rank,
  NULL                                                         AS subject,
  total                                                        AS metric_value,
  CAST(completion_pct AS CHAR)                                 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 >= CURRENT_TIMESTAMP - INTERVAL '12' WEEK
  -- PG: now() - INTERVAL '12 weeks'; MySQL: DATE_SUB(NOW(), INTERVAL 12 WEEK)
  GROUP BY 1
) q2

ORDER BY metric_type, period DESC, rank;

-- Seed 100k task voi PostgreSQL generate_series (PG-specific)
-- MySQL: dung stored procedure loop; SQL Server: WITH RECURSIVE CTE
INSERT INTO tasks (project_id, assignee_id, title, status, created_at, updated_at)
SELECT
  CAST(random() * 5 + 1 AS INT),      -- PG shorthand: (...)::int
  CAST(random() * 10 + 1 AS INT),
  'Task ' || gs,
  CASE WHEN random() < 0.4 THEN 'done' ELSE 'todo' END,
  CURRENT_TIMESTAMP - CAST(random() * 180 AS INT) * INTERVAL '1' DAY,
  CURRENT_TIMESTAMP - CAST(random() * 30 AS INT) * INTERVAL '1' DAY
FROM generate_series(1, 100000) AS gs;

-- Benchmark Query 1 (chay roi do thoi gian o client tool)
-- PostgreSQL: bat \timing truoc khi chay, hoac dung EXPLAIN ANALYZE
-- MySQL: xem SHOW PROFILES hoac dung benchmark tool
-- SQL Server: dung SET STATISTICS TIME ON

WITH monthly AS (
  SELECT
    date_trunc('month', updated_at)                            AS month,
    -- date_trunc: PG/Oracle. MySQL: DATE_FORMAT; SQL Server: DATEADD/DATEDIFF
    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 >= CURRENT_TIMESTAMP - INTERVAL '6' MONTH
    AND assignee_id IS NOT NULL
  GROUP BY 1, 2
),
ranked AS (
  SELECT month, assignee_id, done_count,
         ROUND(CAST(avg_days AS 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;
-- Expected plan: sequential scan -> hash aggregate -> window -> hash join -> filter
-- Estimated: ~150ms tren 100k task; composite index (status, updated_at) giam scan cost

-- 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
CAST(COUNT(*) FILTER (WHERE status = 'done') AS NUMERIC) * 100 / NULLIF(COUNT(*), 0)
-- PG shorthand: ...::numeric * 100 / ...

-- 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)