Interview Question Bank · Aggregate first · FULL OUTER JOIN · COALESCE
5. Source vs warehouse row counts
MediumData engineeringQuestion #245
Concept: Aggregate first · FULL OUTER JOIN · COALESCEshow
To reconcile two systems, count each side per day on its own, then join the two summaries; joining raw rows first multiplies them. A FULL OUTER JOIN keeps days that exist on only one side, and COALESCE turns the missing side's count into 0 and picks whichever date is present.
Example (different data):
WITH a AS (SELECT sold_on, COUNT(*) AS n FROM pos_sales GROUP BY sold_on),
b AS (SELECT sold_on, COUNT(*) AS n FROM ledger_sales GROUP BY sold_on)
SELECT COALESCE(a.sold_on, b.sold_on) AS day,
COALESCE(a.n, 0) AS pos, COALESCE(b.n, 0) AS ledger
FROM a FULL OUTER JOIN b ON b.sold_on = a.sold_on;Your task
Fernhill copies shipments from the operational database (de_src_shipments) into the warehouse (de_wh_shipments) every night. An auditor wants daily proof that both sides hold the same number of rows.
For every day with shipments on either side (by created_at date), return business_date, source_rows, target_rows (count every warehouse row, duplicates included), row_diff (target_rows - source_rows) and status: 'ok' when equal, 'missing' when the warehouse has fewer, 'extra' when it has more. Sort by business_date.
Starting Postgres in your browser…
Checking your access…