0x80Lesson 9 of 9

Practical lab: build a sales report

Combine filtering, grouping and post-aggregation logic in one query.

12 min 5-question quiz 1 code exercise

By the end of this lesson you can

Produce a deterministic monthly report from raw order data.

Real reporting queries often combine staged logic: filter source rows, aggregate metrics, then derive final flags. This lab mimics a production report pipeline where stable output shape and ordering matter.

SQL example

1WITH monthly AS (
2  SELECT month,
3         region,
4         SUM(total) AS revenue
5  FROM sales
6  WHERE status = 'paid'
7  GROUP BY month, region
8)
9SELECT month, region, revenue
10FROM monthly
11ORDER BY month, region;

Deterministic output (ORDER BY) makes reports testable and integration-friendly. Keep business rules explicit in SQL so future readers understand why rows are included.

Key takeaways

Produce a deterministic monthly report from raw order data.
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

Build a monthly paid revenue report

+25 XP

For paid sales only, return month, region, revenue, and a performance label (target when revenue >= 100, otherwise watch). Sort by month then region.

Paid revenue by month and region

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 sales (month TEXT, region TEXT, total REAL, status TEXT); INSERT INTO sales VALUES ('2026-01', 'north', 60, 'paid'), ('2026-01', 'north', 50, 'paid'), ('2026-01', 'south', 40, 'paid'), ('2026-01', 'south', 15, 'pending'), ('2026-02', 'north', 80, 'paid'), ('2026-02', 'south', 130, 'paid');

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…