Master SQL window functions to perform calculations across sets of rows without collapsing them. Learn ROW_NUMBER, RANK, LAG, LEAD, running totals, moving averages, and frame clauses to build powerful analytics queries.
Window functions let you perform calculations across a set of rows related to the current row — without collapsing those rows into one. Start by seeing the problem they solve.
sqlite3 ecommerce.db "SELECT customer_id, order_date, total_amount FROM orders WHERE customer_id IN (1, 2) ORDER BY customer_id, order_date;"sqlite3 ecommerce.db "SELECT customer_id, SUM(total_amount) AS total_spent FROM orders WHERE customer_id IN (1, 2) GROUP BY customer_id;"sqlite3 ecommerce.db "SELECT customer_id, order_date, total_amount, SUM(total_amount) OVER (PARTITION BY customer_id) AS customer_total FROM orders WHERE customer_id IN (1, 2) ORDER BY customer_id, order_date;"The first query shows individual orders — you see every row.
The second query uses GROUP BY to get totals — but it collapses rows, losing the individual order details.
The third query uses a window function: SUM(total_amount) OVER (PARTITION BY customer_id).
It adds the customer's total to every row WITHOUT collapsing them.
You keep the detail AND get the aggregate.
That's the superpower of window functions.
First query: individual orders for customers 1 and 2 with dates and amounts.
Second query: just two rows with total per customer.
Third query: every individual order row, but with an additional column showing that customer's total on every row.