CASE STUDY / A DOCUMENTED SQL DESIGN DECISION
The total looked right. Was the join?
← All case studies01 / The problem
Order totals can be multiplied when joined to line items; guest orders can disappear in an inner customer join.
02 / Technical environment
The SQL Business Analytics project uses 444 synthetic orders and 895 order lines in SQLite.
03 / Investigation
Review the intended aggregation grain and compare order, category and customer-segment totals.
04 / Tools used
SQL joins, CTEs, window functions, SQLite constraints and Python checks.
05 / Findings
Order-level aggregation must happen before customer analysis. A left join preserves guest orders.
06 / Implemented changes
Queries aggregate at order level, retain guests and use integer cents for monetary totals.
07 / Testing and verification
The saved results show five checks and total revenue of $59,578.20. Segment order counts reconcile to all 444 orders.
08 / Results and limitations
Reproducible synthetic analysis. No production dataset or business savings are claimed.
09 / Lesson
Check the meaning of a row before trusting the meaning of a chart.