Window functions nâng cao — Frame, NTILE, Gap-and-island

-- ROWS: physical row count -- luon dem theo so row vat ly
SELECT
  id,
  created_at,
  count(*) OVER (
    ORDER BY created_at
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS last_7_rows
FROM tasks;
-- Luon tinh tren dung 7 row: 6 row truoc + row hien tai
-- Neu data co gap (nhay tu ngay 1 len ngay 10), van tinh tren 7 row vat ly

-- RANGE: value-based -- frame theo gia tri column ORDER BY
SELECT
  id,
  created_at,
  count(*) OVER (
    ORDER BY created_at
    RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
  ) AS rolling_7d
FROM tasks;
-- Tinh tat ca row trong khoang 7 ngay lich, ke ca ngay gap (khong co row)
-- Neu 3 ngay lien tiep khong co task, rolling_7d van chi dem row thuc te co

-- GROUPS: peer group count (chuan SQL, engine moi ho tro)
SELECT
  id,
  score,
  sum(score) OVER (
    ORDER BY score
    GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING
  ) AS group_sum
FROM rankings;
-- GROUPS dem theo nhom peer (cac row co cung gia tri ORDER BY la 1 nhom)
-- 1 PRECEDING = 1 nhom peer truoc, 1 FOLLOWING = 1 nhom peer sau

-- KHONG co ORDER BY trong window:
-- frame mac dinh = RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- -> full partition: moi row trong partition tra ve cung gia tri
SUM(amount) OVER ()
-- = tong toan bo bang (hay toan partition neu co PARTITION BY)

-- CO ORDER BY, KHONG khai bao frame:
-- frame mac dinh = RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
-- -> running total! KHONG phai full partition
SUM(amount) OVER (ORDER BY created_at)
-- Pitfall: nguoi viet tuong la "tong toan partition theo thu tu"
-- Thuc te: moi row nhan tong tich luy den row do (running total)
-- Query chay khong bao loi, ket qua sai silent

-- Force full partition KHI CO ORDER BY:
SUM(amount) OVER (
  ORDER BY created_at
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
-- Hoac: bo ORDER BY neu chi can full partition (khong can thu tu)
SUM(amount) OVER ()

-- Chia user thanh 4 quartile theo so task hoan thanh
SELECT
  user_id,
  completed_count,
  NTILE(4) OVER (ORDER BY completed_count DESC) AS quartile
FROM (
  SELECT
    assignee_id AS user_id,
    count(*)    AS completed_count
  FROM tasks
  WHERE status = 'done'
  GROUP BY assignee_id
) sub;
-- Quartile 1 = top 25% user (nhieu task done nhat)
-- Quartile 4 = bottom 25% user (it task done nhat)
-- NTILE(4) ORDER BY DESC -> quartile 1 la top performer

-- Vi tri tuong doi 0-1 cua moi row trong partition
SELECT
  user_id,
  completed_count,
  PERCENT_RANK() OVER (ORDER BY completed_count) AS pct_rank,
  CUME_DIST()    OVER (ORDER BY completed_count) AS cume_dist
FROM (
  SELECT assignee_id AS user_id, count(*) AS completed_count
  FROM tasks WHERE status = 'done'
  GROUP BY assignee_id
) sub;
-- pct_rank = 0 cho row co gia tri nho nhat, 1 cho gia tri lon nhat
-- cume_dist = % row co gia tri <= gia tri hien tai

-- Practical: tim user o top 10% theo task completion
SELECT user_id, completed_count
FROM (
  SELECT
    assignee_id AS user_id,
    count(*) AS completed_count,
    PERCENT_RANK() OVER (ORDER BY count(*) DESC) AS pct_rank
  FROM tasks WHERE status = 'done'
  GROUP BY assignee_id
) sub
WHERE pct_rank <= 0.10;
-- pct_rank ORDER BY DESC -> pct_rank = 0 la top performer
-- pct_rank <= 0.10 = top 10%

-- "Longest streak ngay lien tuc co task completed per user"
WITH daily_completed AS (
  -- Buoc 1: 1 row per (user, day) -- loai duplicate trong ngay
  SELECT
    assignee_id,
    CAST(date_trunc('day', updated_at) AS DATE) AS day
  FROM tasks
  WHERE status = 'done'
  GROUP BY assignee_id, CAST(date_trunc('day', updated_at) AS DATE)
),
islands AS (
  -- Buoc 2: tinh group key -- hang so cho moi island
  SELECT
    assignee_id,
    day,
    -- KEY: row_number tang cung nhip voi day khi lien tuc
    -- -> hieu la hang so cho toan bo island
    day - CAST(ROW_NUMBER() OVER (
      PARTITION BY assignee_id ORDER BY day
    ) AS INT) * INTERVAL '1' DAY AS grp
  FROM daily_completed
)
-- Buoc 3: dem do dai moi island
SELECT
  assignee_id,
  count(*) AS streak_length,
  min(day) AS streak_start,
  max(day) AS streak_end
FROM islands
GROUP BY assignee_id, grp
ORDER BY streak_length DESC
LIMIT 10;

-- Dashboard: top 10 user co streak hoan thanh task dai nhat (90 ngay gan nhat)
WITH daily_completed AS (
  SELECT assignee_id,
         CAST(date_trunc('day', updated_at) AS DATE) AS day
  FROM tasks
  WHERE status = 'done'
    AND updated_at >= now() - INTERVAL '90 days'
  GROUP BY assignee_id, CAST(date_trunc('day', updated_at) AS DATE)
),
islands AS (
  SELECT assignee_id, day,
         day - CAST(ROW_NUMBER() OVER (
           PARTITION BY assignee_id ORDER BY day
         ) AS INT) * INTERVAL '1' DAY AS grp
  FROM daily_completed
)
SELECT
  assignee_id,
  count(*)    AS streak_length,
  min(day)    AS streak_start,
  max(day)    AS streak_end,
  CASE WHEN max(day) >= current_date - 1 THEN true ELSE false END AS is_active
FROM islands
GROUP BY assignee_id, grp
ORDER BY streak_length DESC
LIMIT 10;

-- Quartile report: phan loai user theo muc do hoat dong
WITH user_stats AS (
  SELECT
    assignee_id,
    count(*) FILTER (WHERE status = 'done') AS done_count,
    CAST(count(*) FILTER (WHERE status = 'done') AS FLOAT)
      / nullif(count(*), 0)                 AS completion_rate
  FROM tasks
  WHERE created_at >= now() - INTERVAL '30 days'
  GROUP BY assignee_id
)
SELECT
  activity_quartile,
  count(*)                            AS user_count,
  round(avg(done_count))              AS avg_done,
  round(CAST(avg(completion_rate) AS NUMERIC), 3) AS avg_rate
FROM (
  SELECT
    assignee_id, done_count, completion_rate,
    NTILE(4) OVER (ORDER BY done_count DESC) AS activity_quartile,
    PERCENT_RANK() OVER (ORDER BY completion_rate) AS rate_pct_rank
  FROM user_stats
) cohort
GROUP BY activity_quartile
ORDER BY activity_quartile;
-- Q1 = top 25% most active, Q4 = bottom 25%
-- Dung cho cohort analysis, onboarding funnel, churn risk model