Search⌘ K
AI Features
Home
Courses
Data Science Bootcamp

Window Functions

Explore SQL window functions to perform calculations across related rows while preserving original data structure. Understand the use of ROW_NUMBER, RANK, SUM, LAG, and LEAD to rank entries, compute cumulative totals, and analyze sequential data in business contexts.

We'll cover the following...

A daily sales table can show one row for 2026-04-03 with revenue of 420, but a manager may also need the month-to-date revenue and the change from 2026-04-02. GROUP BY cannot answer both needs while retaining the 2026-04-03 row. It collapses rows into one summary row per group.

A window function computes a result from related rows while keeping one output row for every input row. The related rows form the window. GROUP BY changes the result’s row count. Window functions add columns to the existing row-level result.

For store-day analysis, that distinction lets one query return store_id, sale_date, revenue, cumulative revenue, and a comparison with the prior day. Before running any query, we can still predict the result grain: one row per store-day.

Number rows within each customer

To calculate across related rows, SQL places the window definition inside OVER(...). ROW_NUMBER() assigns a sequential number, and its OVER clause states which purchases belong together and how to sequence them.

A partition restarts a calculation for each group. In PARTITION BY customer_id, each customer’s purchases receive their own sequence. ORDER BY purchase_date supplies the within-customer order, so the earliest purchase receives 1.

SQL
DROP TABLE IF EXISTS purchases;
CREATE TABLE purchases (
  purchase_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL,
  purchase_date TEXT NOT NULL,
  amount DECIMAL(10, 2) NOT NULL
);
INSERT INTO purchases (purchase_id, customer_id, purchase_date, amount) VALUES
  (1, 101, '2026-04-01', 25.50),
  (2, 101, '2026-04-03', 80.00),
  (3, 101, '2026-04-04', 15.25),
  (4, 101, '2026-04-10', 42.00),
  (5, 102, '2026-03-29', 120.00),
  (6, 102, '2026-04-02', 60.00),
  (7, 102, '2026-04-02', 35.00),
  (8, 103, '2026-04-01', 9.99),
  (9, 103, '2026-04-05', 55.00),
  (10, 103, '2026-04-09', 110.00);
SELECT
  purchase_id,
  customer_id,
  purchase_date,
  amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY purchase_date, purchase_id
  ) AS purchase_number
FROM purchases
ORDER BY customer_id, purchase_date, purchase_id;

  • Lines 1–8: The code recreates the purchases table with a purchase ID, customer ID, purchase date, and amount for each row. ...