Sales & Refund Reconciliation: handouts

Choose Print / Save as PDF, then select Save as PDF in your browser. The learning copy hides answers.

Sales & Refund Reconciliation

Original practice materials · Version 2.0.0 · Synthetic business data

Practice database

CREATE TABLE orders (order_id INTEGER, region TEXT, status TEXT, amount INTEGER, ordered_at TEXT);
INSERT INTO orders VALUES
(4000,'East','pending',1000,'2026-09-01'),
(4001,'West','paid',1125,'2026-10-01'),
(4002,'North','paid',1250,'2026-09-01'),
(4003,'East','paid',1375,'2026-09-04'),
(4004,'West','paid',0,'2026-09-05'),
(4005,'North','paid',1625,'2026-09-06'),
(4006,'East','paid',1750,'2026-09-07'),
(4007,'West','pending',1875,'2026-09-08');
CREATE TABLE refunds (refund_id TEXT, order_id INTEGER, amount INTEGER, status TEXT, booked_at TEXT);
INSERT INTO refunds VALUES
('R4000',4002,100,'requested','2026-09-01'),
('R4001',4003,110,'posted','2026-09-02'),
('R4002',4004,120,'posted','2026-09-03'),
('R4003',4005,130,'posted','2026-09-04'),
('R4004',4006,140,'posted','2026-09-05'),
('R4005',4007,150,'posted','2026-09-06'),
('PART-4000',4003,200,'posted','2026-09-01'),
('OVER-4000',4005,9999,'posted','2026-09-01'),
('ORPH-4000',4999,250,'posted','2026-09-01'),
('END-4000',4006,800,'posted','2026-10-01'),
('R4001',4003,110,'posted','2026-09-02'),
('R4001',4003,110,'posted','2026-09-02'),
('R4002',4004,121,'posted','2026-09-03');

1. Define the reporting cohort

Select the reporting month and keep money in integer cents.

Declare the start inclusive and next month start exclusive.
Filter sale status independently from refund status. A requested refund is not posted money.
Keep the order ID with each sale so a later join can be reconciled.

Select September paid orders

Return order_id, amount sorted by order_id.
Include paid sales from 2026-09-01 inclusive through 2026-10-01 exclusive. Amount is cents.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT order_id,amount FROM orders WHERE status='paid' AND ordered_at>='2026-09-01' AND ordered_at<'2026-10-01' ORDER BY order_id

Filtering only status incorrectly includes the next-month order. A zero sale still belongs to the cohort.

Inspect raw posted refunds in the month

Return refund_id, order_id, amount sorted by refund_id then amount.
This is a raw diagnostic: keep duplicate rows and conflicting variants. Include posted September bookings only.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT refund_id,order_id,amount FROM refunds WHERE status='posted' AND booked_at>='2026-09-01' AND booked_at<'2026-10-01' ORDER BY refund_id,amount

Do not deduplicate this diagnostic: it intentionally exposes every posted source record.

Extension: Compare the eligible order count before and after each join.

2. Resolve refund identity

Separate harmless retries from conflicting refund records.

refund_id is the business key; order_id alone is not, because partial refunds can share a sale.
An exact repeat counts once. A conflicting field quarantines the entire key.
Inspect all records for a key before selecting the reporting period.

Find conflicting refund keys

Return refund_id once for every key with conflicting order_id, amount, status or booked_at. Sort refund_id.
Exact copies alone are not conflicts. All source refund fields are non-NULL.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

SELECT refund_id FROM refunds GROUP BY refund_id HAVING MIN(order_id)<>MAX(order_id) OR MIN(amount)<>MAX(amount) OR MIN(status)<>MAX(status) OR MIN(booked_at)<>MAX(booked_at) ORDER BY refund_id

COUNT(*) > 1 confuses harmless repeats with conflicting facts. Compare the values within each key.

Produce canonical refund records

Return refund_id, order_id, amount, status, booked_at sorted by refund_id.
Keep one exact record per nonconflicting refund_id. Exclude all variants of conflicting keys. Do not apply status/date filters yet.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_refunds AS (
 SELECT DISTINCT r.* FROM refunds r WHERE NOT EXISTS (
  SELECT 1 FROM refunds x WHERE x.refund_id=r.refund_id AND
  (x.order_id<>r.order_id OR x.amount<>r.amount OR x.status<>r.status OR x.booked_at<>r.booked_at)
 )
) SELECT refund_id,order_id,amount,status,booked_at FROM clean_refunds ORDER BY refund_id

First resolve identity over the entire source; only then apply accounting eligibility.

Extension: Add a conflict outside the reporting month and verify that its key is still quarantined.

3. Keep exceptions visible

Find orphan receipts and calculate one refund total per eligible sale.

A refund with no matching order belongs in an exception report.
Summing refunds before joining avoids multiplying gross sales.
Refunds for pending or out-of-month orders are not applied to this report.

List canonical orphan refunds

Return refund_id, order_id for canonical refund records with no matching order. Sort refund_id.
Collapse exact duplicates and exclude conflicts first. No status or date filter is required for this exception report.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_refunds AS (
 SELECT DISTINCT r.* FROM refunds r WHERE NOT EXISTS (
  SELECT 1 FROM refunds x WHERE x.refund_id=r.refund_id AND
  (x.order_id<>r.order_id OR x.amount<>r.amount OR x.status<>r.status OR x.booked_at<>r.booked_at)
 )
) SELECT r.refund_id,r.order_id FROM clean_refunds r LEFT JOIN orders o ON o.order_id=r.order_id WHERE o.order_id IS NULL ORDER BY r.refund_id

An orphan needs follow-up; inventing an order would corrupt the ledger.

Build a per-order refund ledger

Return order_id, amount, refunded, net_cents sorted by order_id. Use the September paid sales cohort.
Apply canonical posted September refunds only. Missing refunds are 0. Net can be negative.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_refunds AS (
 SELECT DISTINCT r.* FROM refunds r WHERE NOT EXISTS (
  SELECT 1 FROM refunds x WHERE x.refund_id=r.refund_id AND
  (x.order_id<>r.order_id OR x.amount<>r.amount OR x.status<>r.status OR x.booked_at<>r.booked_at)
 )
), posted AS (SELECT order_id,SUM(amount) AS refunded FROM clean_refunds WHERE status='posted' AND booked_at>='2026-09-01' AND booked_at<'2026-10-01' GROUP BY order_id), ledger AS (SELECT o.*,COALESCE(p.refunded,0) AS refunded FROM orders o LEFT JOIN posted p ON p.order_id=o.order_id WHERE o.status='paid' AND o.ordered_at>='2026-09-01' AND o.ordered_at<'2026-10-01') SELECT order_id,amount,refunded,amount-refunded AS net_cents FROM ledger ORDER BY order_id

There must be exactly one output row per paid cohort order, even with several refund rows.

Extension: Reconcile applied refunds separately from all posted source money.

4. Reconcile the handoff

Publish checked totals with over-refund exceptions.

Gross minus applied refund cents must equal net cents.
Negative net is a useful exception; clipping it hides an over-refund.
Deliver the query alongside the CSV so a reviewer can reproduce the scope.

Find over-refunded orders

Return order_id, amount, refunded sorted by order_id.
Use the same canonical September ledger. Include refund totals strictly greater than the sale amount.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_refunds AS (
 SELECT DISTINCT r.* FROM refunds r WHERE NOT EXISTS (
  SELECT 1 FROM refunds x WHERE x.refund_id=r.refund_id AND
  (x.order_id<>r.order_id OR x.amount<>r.amount OR x.status<>r.status OR x.booked_at<>r.booked_at)
 )
), posted AS (SELECT order_id,SUM(amount) AS refunded FROM clean_refunds WHERE status='posted' AND booked_at>='2026-09-01' AND booked_at<'2026-10-01' GROUP BY order_id), ledger AS (SELECT o.*,COALESCE(p.refunded,0) AS refunded FROM orders o LEFT JOIN posted p ON p.order_id=o.order_id WHERE o.status='paid' AND o.ordered_at>='2026-09-01' AND o.ordered_at<'2026-10-01') SELECT order_id,amount,refunded FROM ledger WHERE refunded>amount ORDER BY order_id

An exact full refund is not an over-refund. Compare the total of all eligible partial refunds.

Reconcile one company total

Return one row: order_count, gross_cents, refund_cents, net_cents.
Use the canonical September paid-order ledger. Gross minus refund must equal net.

-- Inspect the input tables, then replace this query.
SELECT 1 AS draft;

Reference answer

WITH clean_refunds AS (
 SELECT DISTINCT r.* FROM refunds r WHERE NOT EXISTS (
  SELECT 1 FROM refunds x WHERE x.refund_id=r.refund_id AND
  (x.order_id<>r.order_id OR x.amount<>r.amount OR x.status<>r.status OR x.booked_at<>r.booked_at)
 )
), posted AS (SELECT order_id,SUM(amount) AS refunded FROM clean_refunds WHERE status='posted' AND booked_at>='2026-09-01' AND booked_at<'2026-10-01' GROUP BY order_id), ledger AS (SELECT o.*,COALESCE(p.refunded,0) AS refunded FROM orders o LEFT JOIN posted p ON p.order_id=o.order_id WHERE o.status='paid' AND o.ordered_at>='2026-09-01' AND o.ordered_at<'2026-10-01') SELECT COUNT(*) AS order_count,SUM(amount) AS gross_cents,SUM(refunded) AS refund_cents,SUM(amount-refunded) AS net_cents FROM ledger

Company totals are computed from one row per sale; raw joins make gross too large.

Extension: Document that tax, currency conversion and chargebacks are outside this fictional policy.

Final project

Handoff a reconciled regional ledger

Return region, order_count, gross_cents, refund_cents, net_cents, over_refunded_orders in that order. All monetary values in this project are integer cents. Sort region ascending.
Sales cohort: paid orders with 2026-09-01 <= ordered_at < 2026-10-01. Refund cohort: posted refunds with the same date window applied to booked_at.
Deduplicate refunds by refund_id: identical copies count once; if any order_id, amount, status or booked_at disagrees, exclude every variant of that refund_id.
Aggregate eligible refund amounts by order before joining sales. Orphan refunds and refunds against sales outside the cohort do not affect this report.
No refund means zero. Keep negative net amounts when refunds exceed a sale; do not clamp to zero. Over-refunded means total eligible refunds strictly greater than sale amount.

Use the complete project source data in the course download. Every output check must pass.

WITH clean_refunds AS (
 SELECT DISTINCT r.* FROM refunds r WHERE NOT EXISTS (
  SELECT 1 FROM refunds x WHERE x.refund_id=r.refund_id AND
  (x.order_id<>r.order_id OR x.amount<>r.amount OR x.status<>r.status OR x.booked_at<>r.booked_at)
 )
), posted AS (SELECT order_id,SUM(amount) AS refunded FROM clean_refunds WHERE status='posted' AND booked_at>='2026-09-01' AND booked_at<'2026-10-01' GROUP BY order_id), ledger AS (SELECT o.*,COALESCE(p.refunded,0) AS refunded FROM orders o LEFT JOIN posted p ON p.order_id=o.order_id WHERE o.status='paid' AND o.ordered_at>='2026-09-01' AND o.ordered_at<'2026-10-01') SELECT region,COUNT(*) AS order_count,SUM(amount) AS gross_cents,SUM(refunded) AS refund_cents,SUM(amount-refunded) AS net_cents,SUM(CASE WHEN refunded>amount THEN 1 ELSE 0 END) AS over_refunded_orders FROM ledger GROUP BY region ORDER BY region;

Related public objectives: https://sqlbolt.com/.

Independent original materials. CC BY 4.0.