Aggregating without collapsing the rows

GROUP BY answers questions about groups and destroys the rows to do it. Sometimes you want both: every order, and alongside it the customer's total.

A window function computes across a set of rows while keeping every row.

The shape

SELECT
    id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total,
    amount * 100.0 / SUM(amount) OVER (PARTITION BY customer_id) AS pct
FROM orders;

Every row survives. PARTITION BY is the grouping, and without it the window is the whole result.

Ranking

SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees;

They differ only on ties, and the difference is exactly what gets asked:

RANK leaves a gap after a tie, DENSE_RANK does not, ROW_NUMBER never ties.

Second highest, per group

The classic interview question, and window functions make it routine:

SELECT * FROM (
    SELECT name, department, salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS r
    FROM employees
) ranked
WHERE r = 2;

The subquery is needed because a window function cannot appear in WHERE: at the time WHERE runs, the windows have not been computed.

Looking at neighbouring rows

SELECT order_date, amount,
    LAG(amount) OVER (ORDER BY order_date) AS previous,
    amount - LAG(amount) OVER (ORDER BY order_date) AS change,
    SUM(amount) OVER (ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

LAG and LEAD reach backwards and forwards. A running total is a SUM with a frame that ends at the current row. Day on day change, moving averages and cumulative totals are all this shape, and all of them are otherwise a self join.