205 — Checkpoint: Build a Sales Report

Intermediate

Put all your Module 2 skills to the test by building a comprehensive sales report from scratch. No new concepts — just pure practice combining JOINs, aggregation, subqueries, CASE WHEN, and everything else you've learned, with explicit attention to result grain.

Learning Objectives

Apply JOINs across four tables without guidance

Use GROUP BY with multiple columns for cross-tabulated reports

Filter aggregated results with HAVING

Incorporate subqueries for dynamic comparisons

Add CASE WHEN labels to categorize results

Format and sort a polished final report

Declare the report grain before writing each aggregation step

Step 1

Start blank — explore the data

Before writing the report, take a quick look at the tables you'll be joining. Understand the shape of the data.

Commands to Run

SELECT COUNT(*) AS total_orders FROM orders WHERE status != 'cancelled';

SELECT COUNT(*) AS total_items FROM order_items;

SELECT COUNT(*) AS total_products FROM products;

SELECT COUNT(DISTINCT category_id) AS category_count FROM products;

What This Does

Every report starts with understanding your data. These quick counts tell you the volume you're working with. The sales report will join orders, order_items, products, and categories — so confirming these tables have data is step one. Knowing the category count helps you anticipate how many rows your final report will have.

Expected Outcome

Four counts giving you the size of each table. You'll know roughly how many non-cancelled orders, line items, products, and categories exist in the database.

Pro Tips

1
Professional analysts always explore their data before writing complex queries — it prevents surprises in the results
2
Excluding cancelled orders early (in WHERE, not HAVING) keeps your report accurate

Was this step helpful?