SQL report reconciliation

A database exercise explains why sales can be counted twice and how to check the correct total.

SQL joins · Reporting grain · Reconciliation · Business communication

Prepared for this portfolio using four invented products, five orders, and five campaign records. The values are a teaching example, separate from the Breaking Games project.

Terms explained

Reconciliation: Checking that a report’s totals match the original records.
Reporting grain: What one row represents, such as one product, one order, or one advertising record.
NULL: A missing or unrecorded value. It should not automatically be treated as zero.

Invented data / SQL reporting

The same orders should give the same total.

Revenue · Same scale from $0 to $500

Source ordersThe original total$320

Combine records directlySome sales are counted again$490

Total each source firstThe report matches the source$320

The patterned section represents $170 added by repeated sales. Adding each source by product before combining the tables restores the original $320 total.

Scroll horizontally to compare all columns.

Synthetic reconciliation: source totals versus two reporting approaches
Check	Direct source total	Combine individual records	Total each source first
Product revenue	$320	$490	$320
Campaign spend	$170	$310	$170
Products retained	4	3	4
Spend without recorded signups	$40	Duplicated in the join	$40

Recommendation

Use one row per product before combining the reports.

The order and advertising tables contain multiple records for each product. Combining them directly repeats values. Total each table by product first. Then combine those totals with the product list so products with no activity remain visible.

Define what the metric means

Revenue is quantity × unit price for the five synthetic orders. Campaign spend is the sum of five campaign records. NULL signups mean that no result is recorded. They do not mean zero signups.

Validate before the handoff

The downloadable SQL and Python check make the failure and correction reproducible.

Confirm unique product and order keys.
Compare revenue and spend before and after the join.
Check row counts and products with no activity.
Keep missing campaign results visible in the report.

Explain the business implication

The inflated report could distort product comparisons and budget discussions. The immediate recommendation is to fix the reporting logic and investigate missing measurement before making an allocation decision.

The reporting pattern

The download includes the synthetic tables, the faulty join, and the corrected query.

WITH sales AS (
  SELECT product_id, SUM(quantity * unit_price) AS revenue
  FROM orders GROUP BY product_id
), ads AS (
  SELECT product_id, SUM(spend) AS spend
  FROM campaigns GROUP BY product_id
)
SELECT p.product_id, COALESCE(s.revenue, 0) AS revenue,
       COALESCE(a.spend, 0) AS spend
FROM products p
LEFT JOIN sales s USING (product_id)
LEFT JOIN ads a USING (product_id)

Learning note

Takeaway

A query can run successfully and still answer the wrong question. Reconcile it to the source before using the output to compare products or allocate spend.

Related experience

Breaking Games case study ↗

Explore the related project for its context, approach, and outcome.

Files and calculations.

Download this brief ↓Runnable SQL ↓Verification script ↓

For the full set of sample briefs, CSVs, SQL, verification code, and 14 role briefs, download the collection ↓.