Skip to course content
Free SQL course

SQL for Data Analysis and AI

Module 05 Activity

Scenario

A revenue figure of ₹91,47,789 was circulated last quarter. The true figure was ₹27,01,463. The query that produced it looked completely reasonable. Your job is to reproduce the error, explain it precisely, and write the fix.

Task

  1. Write the join between orders and order_items and total order_total across it. Record what you get.
  2. Narrow to a single order - use order 501 - and show, row by row, why the total is wrong.
  3. Write the corrected query, using pre-aggregation rather than a direct join.
  4. Now the harder half: count customers who have never placed an order. Write it with an inner join

first, notice the answer is wrong, then fix it.

Deliverable

A short incident report: the wrong number, the correct number, the single sentence that explains the cause, the corrected SQL, and one check a reviewer could run in ten seconds to catch it next time.

Check your work

Order 501 has 3 line items and an order_total of exactly ₹1,000.00. Joining to order_items and summing order_total returns ₹3,000.00 for that order alone - the order value is repeated once per line item. Across the table, the join returns 3,400 rows instead of 1,000, and the inflated total is ₹91,47,789 against the true ₹27,01,463.

The one-sentence cause: the join changed the grain from one row per order to one row per line item, and SUM counted the order value once per line.

For the second half, customers who have never ordered: 3,812. An inner join cannot produce this number at all - it discards exactly the rows you are looking for, and returns 0. You need LEFT JOIN orders ... WHERE orders.order_id IS NULL.

The ten-second check

SELECT COUNT(*) before the join and after it. If it changed and you did not intend it to, stop. This one check would have prevented the ₹91,47,789 figure from ever leaving the analyst's laptop.