0x70Lesson 8 of 9

Analyze rows with window functions

Compute ranks and running metrics without collapsing detail rows.

12 min 5-question quiz 1 code exercise

By the end of this lesson you can

Distinguish window functions from GROUP BY aggregates.

Window functions compute values across related rows while keeping each original row in the result. OVER (...) defines the window, and you can partition by group and order inside that group.

SQL example

1SELECT customer_id,
2       order_id,
3       total,
4       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS spend_rank
5FROM orders;

Unlike GROUP BY, window functions do not reduce one group to one row by default. They are ideal for top-N-per-group and running totals.

Key takeaways

Distinguish window functions from GROUP BY aggregates.
Use table and column names that make the query easy to read.
Check which rows a query affects before relying on its result.

Lesson quiz

5 questions · pass with 4 correct · up to 50 XP

Passing this quiz completes the lesson and keeps your streak going. Questions you miss come back in review sessions later.

Practice: write SQL queries

Write queries against small sample databases and run them locally in your browser. Each test starts from a fresh SQLite database.

Exercise 1

Rank orders per customer

+25 XP

Return customer_id, order_id, total and a rank column that numbers orders from highest to lowest total per customer. Sort output by customer_id then rank.

Per-customer ranking

SQL dialectChoose the database engine used when you run tests.

Sample tables and rows

Each test rebuilds an in-memory database for the selected dialect before running your query.

sample-data.sql

CREATE TABLE orders (order_id INTEGER, customer_id INTEGER, total REAL); INSERT INTO orders VALUES (101, 1, 20), (102, 1, 50), (201, 2, 30), (202, 2, 10);

query.sql

Loading editor…

Using SQLite 3 (sql.js), an embedded database. Queries run locally in a worker against a fresh in-memory database for each test.

Questions about this lesson

Stuck? Ask. Figured something out? Share it. Explaining is one of the best ways to learn.

Loading posts…