Skill Workbench
← Workplace tasks

SQLite · About 35 minutes

Explain a sales and refund mismatch

Reconcile paid sales and posted refunds with SQLite. Handle repeated receipts, orphan refunds and over-refunds before exporting a regional ledger.

For junior analysts who know select and need a business query to hand over.

The request

A monthly sales report looks too large after refunds were joined to orders. Establish the reporting cohort, resolve refund identity and produce one ledger row per sale before grouping by region.

Work through it

  1. Use the inclusive opening date and exclusive next-month boundary. Amounts in this task are integer cents.
  2. Collapse exact receipt repeats, quarantine conflicting refund IDs and aggregate eligible refunds before joining to sales.
  3. Reconcile gross minus applied refunds to net. Keep over-refunds visible, then download the SQL, result CSV and handoff report.
Start this task →Try the smaller warm-up

Check before you hand it over

  • A raw one-to-many join can repeat gross sales even when the refund sum looks plausible.
  • An orphan refund needs an exception record; do not invent an order to absorb it.
  • Clipping negative net amounts hides over-refunds.

Included source material

103 original fictional source records. A database setup file, dictionary and output contract. Your browser keeps your work on this device.

Read the output requirements
  • 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.

Related learning

These sources cover related skills. Our task data and materials are original; the providers are not affiliated with this workbench.

Explore Sales & Refund Reconciliation →