A dashboard should answer a question before it draws a chart. Our question is: how did completed orders’ net value change over a selected business-date window, and how does it differ by customer segment or sales channel?
The dataset is an original synthetic workshop shop. It contains no live customers, merchant commissions or CodeRigor revenue. Net value means line quantity × the sale-time price, minus recorded refunds/adjustments associated with the order, assigned to its original business date. This teaching metric is not a financial-statement definition.
You need aggregates, joins, NULL semantics and relationship grain. The exercises are native SQLite SQL; the download supplies the exporter and browser interface.
The first two exercises receive an order_snapshot table at one-order grain. Later you will derive that same grain from raw line/refund tables. For now, select the population without multiplying it.
A NULL filter means all. It is different from selecting the displayed Unknown group. A missing segment becomes Unknown; a missing channel becomes unknown. Keep those orders rather than losing guest or incomplete dimension records through an inner join.
WITH input_orders(id,status,net_cents) AS (
VALUES(1,'completed',0),(2,'completed',1000),(3,'pending',5000)
)
SELECT id,net_cents FROM input_orders
WHERE status='completed' ORDER BY id;The denominator is completed orders, including zero and negative net values. A completed row without line records also remains one order under this explicit exercise contract; data-quality review is separate from silently changing eligibility.
The fixture uses business dates already normalized to YYYY-MM-DD. It does not silently convert event timestamps using the viewer’s local time zone. Dates are inclusive. All records are integer cents before averages or percentage calculations.
Metric names carry units. “Net sales cents” is a sum; “average net order value” divides that sum by completed orders. Distinct customers, lines and positive-value orders are different denominators and answer different questions.
WITH input_orders(id,segment,net_cents) AS (
VALUES(1,NULL,700),(2,'Consumer',300)
)
SELECT id,COALESCE(segment,'Unknown') AS segment,net_cents
FROM input_orders ORDER BY id;Write the eligible order query and inspect the Database tab. The default Jan 1–7 selection contains five completed orders and 5,750 net cents. Some contribute zero; pending/cancelled rows do not contribute.
Next we reconcile aggregates and keep the average’s denominator aligned with that exact row set.