Skill Workbench
← All courses

A regional gross/refund/net ledger and exception SQL

Sales & Refund Reconciliation

Reconcile paid sales to posted refunds without duplicate-key or join inflation.

intermediate · About 110 minutes · 4 lessons · 8 exercises + 1 project

Start lesson →

Your finished work

A regional gross/refund/net ledger and exception SQL

Completion requires every output check to pass. Exact columns, types, values, missing values and duplicate counts all matter. A diagnostic score helps you revise your work.

All examples use original, fictional business data. Your final project starts with 103 source records.

The course

  1. 1. Define the reporting cohort

    Select the reporting month and keep money in integer cents.

    Start lesson →

  2. 2. Resolve refund identity

    Separate harmless retries from conflicting refund records.

    Start lesson →

  3. 3. Keep exceptions visible

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

    Start lesson →

  4. 4. Reconcile the handoff

    Publish checked totals with over-refund exceptions.

    Start lesson →

Put it together

Handoff a reconciled regional ledger

103 source records. Apply the business rules, check your output and download a handoff report with your source, answer and results.

Start the project →Try a fresh dataset →
Get all course files →Explore related public learning objectives →Printable course →

Preview your first input

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');