# SQL report reconciliation

Richie Lin · Portfolio sample · September 2026

Illustrative sample · Synthetic data

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.

## The brief

Why do the same five orders total $320 in one report and $490 in another?

## Worked example

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.


## Learning note

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.

## SQL pattern

```sql
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)
```

## Files and sources

- Runnable SQL: sql-quality-check.sql
- Verification script: verify-samples.py


Related experience: https://richielin.com/pages/breaking-games

View online: https://richielin.com/pages/samples/sql-quality-check