SQL Window Functions Explained: OVER, PARTITION BY, Running Totals, and LAG

GROUP BY summarizes rows and collapses detail. Window functions are different: they keep every detail row visible and add calculations beside it.

In SQL for Files, you can practice these patterns on sample sales data and then reuse them on local files in your browser with DuckDB WASM.

OVER () means the whole result set is the calculation window. PARTITION BY splits that window into smaller groups, such as one window per region.

SELECT id, region, amount,
  SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales_2;

ORDER BY inside a window makes row sequence matter. That is how you build running totals, rankings, and time-based comparisons.

SELECT salesperson, sale_date, amount,
  SUM(amount) OVER (
    PARTITION BY salesperson
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_amount
FROM sales_2;

ROW_NUMBER ranks rows inside each group. LAG looks backward to a previous row. Together, they support top-N queries, deduplication, and change-over-time analysis.

SELECT salesperson, sale_date, amount,
  LAG(amount) OVER (PARTITION BY salesperson ORDER BY sale_date) AS previous_amount
FROM sales_2;

1. Use OVER () to add a company-wide total beside each row.
2. Use PARTITION BY for region-specific totals.
3. Add ORDER BY and a frame for running totals.
4. Practice ROW_NUMBER, LAG, and other common window functions.