From: Dana Whitfield, finance lead
Deliverable: one query that returns the monthly report, ready to save and re-run
"Every month I rebuild the same numbers by hand. I'd like one query that gives me, for each month of 2025: orders, revenue and average order; how many customers ordered for the first time; how much revenue came from repeat customers and what share that is; the change in revenue from the month before; the best-selling category; and revenue so far this year.
Leave cancelled orders out of everything. If two categories tie for best seller, take the one that comes first alphabetically."
Data: orders, order_items, products.
How this works: each step starts from your query from the step before, so you build the report up one piece at a time. Keep the columns in the order the steps add them.
A project is a piece of real work done in steps. This one builds a single report query from a simple monthly summary into a full report with a dozen columns, the kind of query an analyst saves and re-runs every month.
The query grows with CTEs: one named step for each building block (first orders, repeat revenue, best category) and a final SELECT that joins them to the monthly totals. Joining every block to monthly with a LEFT JOIN keeps months where a block has no rows, and COALESCE turns those gaps into 0.
Each step is checked by the rows the query returns, and the next step starts from your own query, so a fix you make carries forward.