Window Functions: Rankings, Running Totals & Neighbor Rows

The shape: function(...) OVER (PARTITION BY ... ORDER BY ...). PARTITION BY splits the rows into independent groups (like GROUP BY, except the rows survive) and the ORDER BY inside OVER sets the order within each group. Both parts are optional.

Aggregates as windows. Add OVER to an aggregate and its result is attached to every row. 'Each order's share of the total' needs no self-join:

SELECT id,
       amount,
       SUM(amount) OVER ()                              AS grand_total,
       ROUND(100.0 * amount / SUM(amount) OVER (), 1)   AS pct_of_total
FROM orders;

Running totals. Put an ORDER BY inside OVER and the aggregate accumulates up to the current row. To find the moment a total crossed a threshold, compute the running total in a CTE and filter on it outside (rows with identical ORDER BY values share the same running total):

SELECT created_at,
       amount,
       SUM(amount) OVER (ORDER BY created_at) AS running_total
FROM orders;

Numbering and ranking. ROW_NUMBER() gives 1, 2, 3, ... with no ties. RANK() gives tied rows the same rank and then skips (1, 2, 2, 4). DENSE_RANK() gives ties the same rank without skipping (1, 2, 2, 3). Add PARTITION BY to restart the numbering for each group, which is the standard way to get the top row per group:

WITH ranked AS (
  SELECT customer_id, id, amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY amount DESC) AS rn
  FROM orders
)
SELECT customer_id, id, amount
FROM ranked
WHERE rn = 1;

You cannot filter on a window function in the same SELECT's WHERE (WHERE runs before windows are computed), which is why the query wraps it in a CTE and filters outside.

Looking at neighbors. LAG(col) reads the previous row's value and LEAD(col) the next row's, in the window's order - ideal for gaps and changes. The first row has no previous row, so LAG returns NULL there (or a default you supply as LAG(col, 1, default)):

SELECT id, created_at,
       julianday(created_at)
         - julianday(LAG(created_at) OVER (ORDER BY created_at)) AS days_since_previous
FROM orders;

First or last per group. MIN(created_at) OVER (PARTITION BY customer_id) attaches each customer's first-order time to every one of their rows, so each row can be compared with its group's first.

Which tool? Use GROUP BY when you want one row per group. Use a window function when you want to keep every row and add group-level context. Both avoid the per-row re-scan of a correlated subquery. The SQL Lab's engine supports window functions, and levels 41-50 are built on them.