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.