Window Functions

-- 1) Basic syntax — OVER() is what makes it a window function
SELECT
    customer_id,
    order_id,
    total_cents,
    SUM(total_cents) OVER ()                            AS overall_total,
    SUM(total_cents) OVER (PARTITION BY customer_id)    AS customer_total,
    AVG(total_cents) OVER (PARTITION BY customer_id)    AS customer_avg
FROM orders;

-- The query still returns ONE row per source row.
-- PARTITION BY splits rows into groups that don't talk to each other.

-- 2) Ordering inside a window
SELECT
    customer_id,
    order_id,
    created_at,
    SUM(total_cents) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

-- A 'frame' = the slice of the partition the function sees.
-- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  = everything from the start up to (and including) this row.

-- 3) Rankings — ROW_NUMBER, RANK, DENSE_RANK
SELECT
    customer_id,
    order_id,
    total_cents,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rn,
    RANK()       OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total_cents DESC) AS drnk
FROM orders;

-- ROW_NUMBER  — unique, 1..n, no gaps on ties
-- RANK        — ties get same number, next rank SKIPS (1,2,2,4)
-- DENSE_RANK  — ties get same number, next rank +1 (1,2,2,3)

-- 4) Top-N per group (most common interview / report question)
WITH ranked AS (
    SELECT
        customer_id,
        order_id,
        total_cents,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
    FROM orders
)
SELECT customer_id, order_id, total_cents
FROM ranked
WHERE rn <= 3;
-- Most recent 3 orders per customer. Pre-window-function MySQL needed nasty self-joins.

-- 5) LAG and LEAD — peek at neighbouring rows
SELECT
    customer_id,
    created_at,
    total_cents,
    LAG(total_cents)  OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_amount,
    LEAD(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS next_amount,
    total_cents - LAG(total_cents) OVER (PARTITION BY customer_id ORDER BY created_at) AS delta
FROM orders;

-- Useful for: time-series differencing, churn detection, gap analysis.

-- 6) NTILE — buckets / quartiles / deciles
SELECT
    customer_id,
    SUM(total_cents) AS spend,
    NTILE(4) OVER (ORDER BY SUM(total_cents) DESC) AS quartile
FROM orders
GROUP BY customer_id;
-- Now you have customers labelled 1 (top 25%) through 4 (bottom 25%).

-- 7) FIRST_VALUE / LAST_VALUE / NTH_VALUE
SELECT
    customer_id,
    order_id,
    created_at,
    FIRST_VALUE(order_id) OVER (PARTITION BY customer_id ORDER BY created_at) AS first_order,
    LAST_VALUE(order_id)  OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order
FROM orders;

-- WARNING: default frame for ORDER BY-only is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.
-- LAST_VALUE without an explicit ROWS clause returns the CURRENT row, not the last in partition!

-- 8) Frame specs
-- ROWS BETWEEN <x> PRECEDING AND <y> FOLLOWING
--   1 PRECEDING AND 1 FOLLOWING  — 3-row centered window (moving average)
--   UNBOUNDED PRECEDING AND CURRENT ROW — running total
--   UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING — whole partition
--   CURRENT ROW AND 6 FOLLOWING — week ahead
--
-- RANGE BETWEEN works on ORDER BY VALUE proximity, not row count. Use ROWS for predictability.

-- 9) Moving average — last 7 orders
SELECT
    customer_id,
    created_at,
    AVG(total_cents) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS avg_last_7
FROM orders;

-- 10) Percentile and cumulative distribution
SELECT
    customer_id,
    SUM(total_cents) AS spend,
    CUME_DIST() OVER (ORDER BY SUM(total_cents)) AS cumulative_pct,
    PERCENT_RANK() OVER (ORDER BY SUM(total_cents)) AS pct_rank
FROM orders
GROUP BY customer_id;

-- 11) Multiple windows — name them with WINDOW clause for readability
SELECT
    customer_id,
    order_id,
    ROW_NUMBER() OVER w AS rn,
    SUM(total_cents) OVER w AS running
FROM orders
WINDOW w AS (PARTITION BY customer_id ORDER BY created_at);

-- 12) Window functions vs aggregates vs subqueries
-- Aggregates collapse rows into groups (loss of detail).
-- Subqueries can replicate window logic but tend to be slower and harder to read.
-- Window functions keep every row + add a computed column.

-- 13) Common patterns by use case
-- Sessionization
SELECT
    user_id,
    event_at,
    SUM(CASE WHEN TIMESTAMPDIFF(SECOND, prev_at, event_at) > 1800 OR prev_at IS NULL THEN 1 ELSE 0 END)
        OVER (PARTITION BY user_id ORDER BY event_at) AS session_id
FROM (
    SELECT user_id, event_at,
                 LAG(event_at) OVER (PARTITION BY user_id ORDER BY event_at) AS prev_at
    FROM events
) t;

-- Deduplication (keep newest per key)
WITH ranked AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE u FROM users u
JOIN ranked r ON r.id = u.id AND r.rn > 1;

-- 14) Performance
-- • Window functions execute AFTER WHERE but BEFORE ORDER BY in the outer query
-- • You can't use them in WHERE — wrap in a subquery / CTE and filter outside
-- • Indexing helps: index on (partition columns, order columns)
-- • Many windows on the same query can be deduplicated with shared WINDOW definitions
-- • EXPLAIN shows 'Window' rows in MySQL 8 plan output — check for sort and buffer use

-- 15) MySQL 8 specifics
-- • Available since 8.0 (NOT in MySQL 5.7)
-- • If you're stuck on MySQL 5.7: emulate with self-joins or upgrade — performance and clarity wins are massive
-- • Aurora MySQL 3, PlanetScale, RDS MySQL 8 — all supported

-- 16) Common bugs
-- • LAST_VALUE returning current row → add explicit ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
-- • Filtering on a window function in WHERE → wrap in CTE / subquery
-- • RANK vs DENSE_RANK confusion → use ROW_NUMBER if you need a strict 1..N with no ties
-- • Mixing GROUP BY and window functions on the same level — windows see post-aggregation rows
-- • Window without ORDER BY but expecting sequence — UNBOUNDED frame ignores order; results are non-deterministic
-- • Heavy windowed queries on production transactional tables — consider read replicas / analytics warehouse
-- • Window over very large partitions — sorts can spill to disk; tune tmp_table_size / sort_buffer_size

SELECT name, salary,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS r,
    AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;