Capstone project

Capstone: a monthly sales dashboard

Lesson 20 of 20 · 28 min · Module 6: Performance and the capstone

Build the query behind a real sales dashboard: for each month, how many orders and customers, how many of them were new, how much revenue, and how it changed compared with the previous month.

Start this lesson →

Free. No installation: you write and run the code in your browser, and it is checked automatically.

By the end, you’ll be able to…

Plan a complex query as named stages
Combine aggregates, subqueries and windows
Calculate period-over-period growth

The idea

Count only paid and shipped orders. A customer is new in the month of their first such order. Growth compares revenue with the previous month using LAG, and is NULL for the first month.

SELECT customer_id, MIN(strftime('%Y-%m', ordered_at)) AS first_month
FROM orders
WHERE status IN ('paid', 'shipped')
GROUP BY customer_id;

01Design the stages first

Write down the questions in order: what is each order worth and when, when did each customer start, what happened each month, and how does each month compare with the previous one. Each answer becomes one CTE.

02Growth is relative

Growth = (this month − last month) ÷ last month × 100. LAG supplies last month’s revenue on the same row, and NULL for the first month makes the growth NULL too, which is honest: there is nothing to compare with.

SELECT ROUND(100.0 * (150 - 120) / 120, 1);

Output

25.0

03Check each stage

Run each CTE on its own while building, for example SELECT * FROM first_month. Small, verified steps are how analysts trust big queries.

Your project brief

Return one row per month with month (YYYY-MM), orders, customers (distinct customers who ordered), new_customers, revenue_cents, and growth_pct: the percentage change in revenue from the previous month, rounded to one decimal (NULL for the first month). Sort by month.

What the checks look for

Reports orders, customers, new customers, revenue and growth for each month
Leaves the data unchanged

Try it in the editor →

Take this with you

Real reports are pipelines of small, verified queries.

Go a little further

Add a returning_customers column (customers who ordered this month and in an earlier month) and the average order value per month.