SQL Window Functions — Phân Tích Dữ Liệu Không Cần Subquery

-- GROUP BY — gộp dòng, mất chi tiết
SELECT department, AVG(salary)
FROM employees
GROUP BY department;

-- Window function — giữ nguyên dòng, thêm cột tính toán
SELECT name, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

SELECT
  name,
  department,
  salary,
  -- AVG trong từng department
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  -- Global AVG
  AVG(salary) OVER () AS company_avg,
  -- Salary so với department average
  ROUND(salary - AVG(salary) OVER (PARTITION BY department)) AS diff_from_dept_avg
FROM employees;

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- Top 3 nhân viên lương cao nhất mỗi phòng ban
WITH ranked AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3;

SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day_revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
  LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue
FROM daily_revenue
ORDER BY date;

-- Tính thời gian giữa các hành động của user
SELECT
  user_id,
  action,
  created_at,
  LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_action,
  EXTRACT(EPOCH FROM created_at - LAG(created_at)
    OVER (PARTITION BY user_id ORDER BY created_at)) AS seconds_since_prev
FROM user_actions;

SELECT
  department,
  name,
  salary,
  FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) AS highest_paid,
  LAST_VALUE(name) OVER (
    PARTITION BY department
    ORDER BY salary DESC
    RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_paid
FROM employees;

SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  SUM(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS weekly_moving_avg
FROM daily_revenue;

-- Chia employees thành 4 nhóm (quartile) theo lương
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS salary_quartile
FROM employees;

-- Phân trang API dùng NTILE
WITH page_groups AS (
  SELECT *,
    NTILE(10) OVER (ORDER BY id) AS page
  FROM products
)
SELECT * FROM page_groups WHERE page = 1;

SELECT
  score,
  CUME_DIST() OVER (ORDER BY score DESC) AS cumulative_dist,  -- % của scores <= hiện tại
  PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank    -- relative rank (0-1)
FROM exam_scores;

WITH changes AS (
  SELECT
    user_id,
    status,
    created_at,
    LAG(status) OVER (PARTITION BY user_id ORDER BY created_at) AS prev_status
  FROM subscriptions
)
SELECT *
FROM changes
WHERE status != prev_status OR prev_status IS NULL;

-- Tạo date series và fill missing values
WITH dates AS (
  SELECT generate_series(
    '2024-01-01'::date,
    '2024-01-31'::date,
    '1 day'::interval
  ) AS date
)
SELECT
  dates.date,
  COALESCE(sales.amount, 0) AS amount,
  SUM(COALESCE(sales.amount, 0)) OVER (ORDER BY dates.date) AS running_total
FROM dates
LEFT JOIN sales ON sales.date = dates.date
ORDER BY dates.date;

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date)::date AS month,
    SUM(amount) AS revenue
  FROM orders
  GROUP BY month
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 2) AS growth_pct
FROM monthly;

SELECT
  user_id,
  order_date,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date) AS order_rank
FROM orders;
-- WHERE order_rank = 1 → first purchase

-- Window functions cần sort — đảm bảo có index phù hợp
CREATE INDEX idx_employees_dept_salary ON employees(department, salary DESC);
CREATE INDEX idx_daily_revenue_date ON daily_revenue(date);