Capstone: The Report

A warehouse is built to answer questions. In this last capstone lesson you finish build_report(). It makes daily_revenue from the star, one row per day of the week: the day's orders, the money its paid orders brought, and the share that was cancelled. Then it prints the week's top 3 products and ends with one sentence a manager can read. At work this is the part people see, so every number must come straight from the checked star.

Adding up only some rows: CASE WHEN … THEN … ELSE … END gives each row its own value, and SUM adds those values. Put the CASE inside the SUM and only the rows you pick add something, while the other columns of the same query still see every row. A WHERE would drop those rows from every column. The same trick gives a share: the AVG of ones and zeros is the share of ones, and ROUND(x, 2) keeps 2 decimals. Here is an example on another column, counting the orders with more than one item:

SELECT f.date,
    SUM(CASE WHEN f.quantity > 1 THEN 1 ELSE 0 END) AS multi_item,
    ROUND(AVG(CASE WHEN f.quantity > 1 THEN 1 ELSE 0 END), 2) AS multi_share
FROM fact_orders f
GROUP BY f.date

Top N: ORDER BY amount DESC sorts from the biggest down and LIMIT 5 keeps the first 5 rows, so SELECT order_id, amount FROM fact_orders ORDER BY amount DESC LIMIT 5 lists the 5 biggest orders. Without the ORDER BY, LIMIT just keeps whichever rows happen to come out first. In Python, con.execute(query).fetchall() returns the rows as a list of tuples, so top[0] is the first row and top[0][0] is its first value.

Words from numbers: build the closing sentence with str() and + from the query results, never from numbers you typed in. At work the data changes every day, and a typed number keeps telling an old story. The check tests this: it cancels every Gift card order and calls build_report() again. The report also runs only after run_checks(), which raises an error when a check fails, so nobody reads numbers from a star that failed its checks.

Your finished code prints:

fact keys unique: PASS
no missing customer: PASS
every product_key in dim_product: PASS
source and fact totals agree: PASS
Gift card: 750
Lamp: 464
Teapot: 352
Paid orders brought 2224 this week, led by Gift card.

The check then prints daily_revenue, and after it cancels the Gift card orders, what the second call of build_report() prints:

date | day_name | orders | revenue | cancelled_share
2026-05-01 | Friday | 10 | 274 | 0
2026-05-02 | Saturday | 13 | 384 | 0.15
2026-05-03 | Sunday | 10 | 245 | 0.2
2026-05-04 | Monday | 8 | 122 | 0.25
2026-05-05 | Tuesday | 12 | 436 | 0
2026-05-06 | Wednesday | 9 | 168 | 0.33
2026-05-07 | Thursday | 11 | 595 | 0.18
(7 rows)

Lamp: 464
Teapot: 352
Plant: 255
Paid orders brought 1474 this week, led by Lamp.

Your task: the whole pipeline is in the starting code, and build_report() is half built. Add revenue (paid amounts only), orders and cancelled_share to daily_revenue. Sort the top products query by revenue from the biggest down and keep 3 rows. Print the closing sentence, and call build_report() below run_checks().

Assignment
Follow the Coding Tutorial to finish build_report(): the three columns of daily_revenue, the top 3 products, the closing sentence, and the call that runs the report once the checks pass.

Hint
A cancelled order adds 0 to revenue but still counts in orders, so pick the paid rows with a CASE inside SUM, not with a WHERE. Sort before you cut. top[0][0] is the best seller's name, and str(total) turns the week's total into text.