Final project · SQLite

Handoff a reconciled regional ledger

Project dataset

My saved work →

Project dataset Fresh dataset challenge →Each dataset keeps its own attempts.

Your task

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.

Pass every output check to complete this task. Suggested active time: 10 minutes. You can keep working after that.

Required output

Return exactly these columns in this order.

regionstring
order_countnumber
gross_centsnumber
refund_centsnumber
net_centsnumber
over_refunded_ordersnumber

Expected output: 3 rows. Keep the requested row order.

Values, data types and the number of copies of each row must match. A missing value is distinct from zero and from an empty string.

Preserve SQL NULL unless the task explicitly gives a replacement rule. Check JOINs for extra copies of rows.

Reconciliation checks

Cohort: 53 paid orders. Gross cents: 264000; applied refund cents: 16279; net cents: 247721.
Gross minus applied refunds must equal net; joining raw refund rows would inflate gross.
Keep orphan and conflicting refund IDs available for a separate audit; they are excluded from the financial aggregate, not silently repaired.

Need a next step?

Hint 1: where to start

Establish the business-key and date policies before joining or aggregating.

Hint 2: build the method

Write down the eligible keys before aggregating. Inspect each anomaly against the output contract.

Hint 3: check your work

Use the reconciliation counts after constructing the complete output; a matching total alone does not prove the rows are correct.

How your work is checked

Every output check must pass. The score shows how much matched and helps you find mistakes; completion comes from passing all checks. Active time is recorded separately.

Passing records a correct practice output on this device. It is not a professional certification.

Source materials

103 original source records.

Data dictionary

orders.order_id integer: Unique sale key; an unmatched refund must not create a sale.
orders.region text: Sales reporting region.
orders.status text: Only paid sales qualify.
orders.amount integer: Sale value in cents; includes zero.
orders.ordered_at text: ISO sale date; select the half-open reporting month.
refunds.refund_id text: Business key. Collapse exact repeats; exclude all conflicting variants.
refunds.order_id integer: May be orphaned or refer to a sale outside the cohort.
refunds.amount integer: Positive refunded cents; multiple distinct refunds may belong to one order.
refunds.status text: Only posted refunds reduce revenue.
refunds.booked_at text: ISO booking date; apply the same half-open reporting month.

View database setup and records

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'),
(4008,'North','paid',2000,'2026-09-09'),
(4009,'East','paid',2125,'2026-09-10'),
(4010,'West','paid',2250,'2026-09-11'),
(4011,'North','paid',2375,'2026-09-12'),
(4012,'East','paid',2500,'2026-09-13'),
(4013,'West','paid',2625,'2026-09-14'),
(4014,'North','pending',2750,'2026-09-15'),
(4015,'East','paid',2875,'2026-09-16'),
(4016,'West','paid',3000,'2026-09-17'),
(4017,'North','paid',3125,'2026-09-18'),
(4018,'East','paid',3250,'2026-09-19'),
(4019,'West','paid',3375,'2026-09-20'),
(4020,'North','paid',3500,'2026-09-21'),
(4021,'East','pending',3625,'2026-09-22'),
(4022,'West','paid',3750,'2026-09-23'),
(4023,'North','paid',3875,'2026-09-24'),
(4024,'East','paid',4000,'2026-09-25'),
(4025,'West','paid',4125,'2026-09-26'),
(4026,'North','paid',4250,'2026-09-27'),
(4027,'East','paid',4375,'2026-09-28'),
(4028,'West','pending',4500,'2026-09-01'),
(4029,'North','paid',4625,'2026-09-02'),
(4030,'East','paid',4750,'2026-09-03'),
(4031,'West','paid',4875,'2026-09-04'),
(4032,'North','paid',5000,'2026-09-05'),
(4033,'East','paid',5125,'2026-09-06'),
(4034,'West','paid',5250,'2026-09-07'),
(4035,'North','pending',5375,'2026-09-08'),
(4036,'East','paid',5500,'2026-09-09'),
(4037,'West','paid',5625,'2026-09-10'),
(4038,'North','paid',5750,'2026-09-11'),
(4039,'East','paid',5875,'2026-09-12'),
(4040,'West','paid',6000,'2026-09-13'),
(4041,'North','paid',6125,'2026-09-14'),
(4042,'East','pending',6250,'2026-09-15'),
(4043,'West','paid',6375,'2026-09-16'),
(4044,'North','paid',6500,'2026-09-17'),
(4045,'East','paid',6625,'2026-09-18'),
(4046,'West','paid',6750,'2026-09-19'),
(4047,'North','paid',6875,'2026-09-20'),
(4048,'East','paid',7000,'2026-09-21'),
(4049,'West','pending',7125,'2026-09-22'),
(4050,'North','paid',7250,'2026-09-23'),
(4051,'East','paid',7375,'2026-09-24'),
(4052,'West','paid',7500,'2026-09-25'),
(4053,'North','paid',7625,'2026-09-26'),
(4054,'East','paid',7750,'2026-09-27'),
(4055,'West','paid',7875,'2026-09-28'),
(4056,'North','pending',8000,'2026-09-01'),
(4057,'East','paid',8125,'2026-09-02'),
(4058,'West','paid',8250,'2026-09-03'),
(4059,'North','paid',8375,'2026-09-04'),
(4060,'East','paid',8500,'2026-09-05'),
(4061,'West','paid',8625,'2026-09-06'),
(4062,'North','paid',8750,'2026-09-07'),
(4063,'East','pending',8875,'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'),
('R4006',4008,160,'posted','2026-09-07'),
('R4007',4009,170,'posted','2026-09-08'),
('R4008',4010,180,'posted','2026-09-09'),
('R4009',4011,190,'requested','2026-09-10'),
('R4010',4012,200,'posted','2026-09-11'),
('R4011',4013,210,'posted','2026-09-12'),
('R4012',4014,220,'posted','2026-09-13'),
('R4013',4015,230,'posted','2026-09-14'),
('R4014',4016,240,'posted','2026-09-15'),
('R4015',4017,250,'posted','2026-09-16'),
('R4016',4018,260,'posted','2026-09-17'),
('R4017',4019,270,'posted','2026-09-18'),
('R4018',4020,280,'requested','2026-09-19'),
('R4019',4021,290,'posted','2026-09-20'),
('R4020',4022,300,'posted','2026-09-21'),
('R4021',4023,310,'posted','2026-09-22'),
('R4022',4024,320,'posted','2026-09-23'),
('R4023',4025,330,'posted','2026-09-24'),
('R4024',4026,340,'posted','2026-09-25'),
('R4025',4027,350,'posted','2026-09-26'),
('R4026',4028,360,'posted','2026-09-27'),
('R4027',4029,370,'requested','2026-09-28'),
('R4028',4030,380,'posted','2026-09-01'),
('R4029',4031,390,'posted','2026-09-02'),
('R4030',4032,400,'posted','2026-09-03'),
('R4031',4033,410,'posted','2026-09-04'),
('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');

Your work

Active 0:00 · Paused 0s

SQL query

Import answer

Keep a handoff you can replay

Check your current answer, then download the output and its report. The report includes the source, answer and each check so you can review what passed and what needs work.

To replay your work, open this task with the same dataset, import your answer and check it again.

Show the 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 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;

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