Window Functions

-- 1) Number rows within a group
SELECT id, user_id, total,
       ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) AS rn
FROM orders;

-- Top-3 orders per user — wrap in a subquery
SELECT * FROM (
    SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) rn
    FROM orders o
) t
WHERE rn <= 3;

-- 2) RANK / DENSE_RANK
SELECT name, score,
       RANK()       OVER (ORDER BY score DESC) AS r,
       DENSE_RANK() OVER (ORDER BY score DESC) AS dr
FROM leaderboard;
-- Scores 100, 100, 90, 80 →
--   RANK:        1, 1, 3, 4
--   DENSE_RANK:  1, 1, 2, 3

-- 3) Running totals
SELECT created_at, amount,
       SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM transactions;

-- 4) Running total within groups
SELECT user_id, created_at, amount,
       SUM(amount) OVER (PARTITION BY user_id ORDER BY created_at) AS user_total
FROM transactions;

-- 5) Moving averages
SELECT date, value,
       AVG(value) OVER (
           ORDER BY date
           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
       ) AS ma_7day
FROM stock_prices;

-- 6) LAG / LEAD — previous + next row
SELECT date, total,
       LAG(total, 1)  OVER (ORDER BY date) AS prev_total,
       LEAD(total, 1) OVER (ORDER BY date) AS next_total,
       total - LAG(total, 1) OVER (ORDER BY date) AS day_over_day
FROM revenue_daily;

-- 7) NTILE — buckets / quartiles
SELECT name, salary,
       NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

-- 8) FIRST_VALUE / LAST_VALUE — windowed first / last
SELECT user_id, total, created_at,
       FIRST_VALUE(total) OVER (PARTITION BY user_id ORDER BY created_at) AS first_order,
       LAST_VALUE (total) OVER (
           PARTITION BY user_id ORDER BY created_at
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_order
FROM orders;

-- 9) Reuse a window definition with WINDOW clause
SELECT name, score,
       RANK()       OVER w AS rank,
       DENSE_RANK() OVER w AS dense_rank,
       PERCENT_RANK() OVER w AS pct
FROM scores
WINDOW w AS (ORDER BY score DESC);

-- 10) Frame specifications
--   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW   — running total (default for ordered)
--   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW           — last 7 rows
--   RANGE BETWEEN INTERVAL '7 day' PRECEDING AND CURRENT ROW   — last 7 days by date
--   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING   — whole partition

-- 11) Real-world: percent of group
SELECT user_id, total,
       total * 100.0 / SUM(total) OVER (PARTITION BY user_id) AS pct_of_user
FROM orders;

-- 12) Detect gaps in a sequence
SELECT id,
       id - ROW_NUMBER() OVER (ORDER BY id) AS gap_group
FROM events;
-- Rows with the same gap_group form a contiguous run.

-- 13) Performance
--   • Window functions read every row in the partition — index PARTITION BY + ORDER BY columns
--   • Avoid mixing with GROUP BY in the same SELECT unless you understand the semantics
--   • EXPLAIN ANALYZE — look for WindowAgg nodes; their input rows should be ordered already

-- 14) Combine with CTE for readability
WITH t AS (
    SELECT user_id, total, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total DESC) rn
    FROM orders
)
SELECT * FROM t WHERE rn <= 3;

SELECT name, salary,
    rank() OVER (PARTITION BY department ORDER BY salary DESC) AS r,
    avg(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;