Window Functions: ROW_NUMBER, RANK, LEAD, LAG

Rank first to pin down the latest row, then test its status – filtering the status before ranking changes which row counts as "latest" and gives the wrong answer.

Greatest-N-per-group needs a per-group rank via `ROW_NUMBER`/`RANK`/`DENSE_RANK` over `PARTITION BY`, then filter to N – a plain `ORDER BY ... LIMIT N` only gives a global top-N.

Replicas lag behind the primary, so after a user writes, route that user's reads to the primary until the replica catches up.

Filter `salary > (SELECT AVG(salary) ...)`, then `ORDER BY salary DESC LIMIT 5`, or use `RANK()`/`DENSE_RANK()` and keep ranks `<= 5` to handle ties.

`ROW_NUMBER() OVER (PARTITION BY customer ORDER BY ...)` then filter `= 1` for the top row per customer, or `<= 3` for the top three.

Distinct customer/day, then `LAG(day)` within each customer ordered by day, and keep where today minus the previous day equals one.

Window functions add per-row analytics like running totals, previous/next values, and ranks over a defined window, without collapsing the rows the way GROUP BY does.

`ROW_NUMBER` is always unique, `RANK` ties then skips leaving gaps, and `DENSE_RANK` ties without gaps.