Articles
SQL Revenue Reconciliation Interview Questions: Orders, Payments, Refunds, and Mismatches
Practise SQL revenue reconciliation with orders, payments, partial refunds, cutoffs, currencies, missing records, and reason-coded mismatch checks.
Three tables can agree in total and still disagree row by row. An order for $100 may have two successful payments, two partial refunds, a failed refund, and a payout days later. If you join every record and sum once, the query can multiply money while looking perfectly reasonable.
A strong SQL revenue reconciliation answer defines the grain and equations before writing the join. Then it pre-aggregates each many-side, preserves missing records, applies one cutoff rule, and assigns an actionable reason to every difference. Practise that diagnostic mindset with Design Transaction Data Quality Checks.
Evidence boundary: Payment-provider behavior below comes from official Stripe documentation checked on September 19, 2026. The schema, fixtures, SQL, and interview prompts are original teaching examples. “Revenue” here means an operational order-to-cash comparison, not an accounting policy for recognized revenue.
What exactly are you reconciling?
Start with three separate questions. Did the customer pay the amount expected for the order? Were all successful refunds represented correctly? Does retained cash equal successful payments minus successful refunds? Those are different equations.
Assume money is stored in a currency's minor unit and every row has a currency. For each order:
| Check | Expected equation |
|---|---|
| Gross collection | successful payments = order total |
| Refund integrity | successful refunds ≤ successful payments |
| Retained cash | successful payments − successful refunds |
| Net order value | order total − accepted order-level refund amount |
The last two values can be compared only if “accepted order-level refund amount” and successful processor refunds have the same business meaning and cutoff. If the order system records an approved refund before the processor succeeds, a temporary difference is expected.
Ask whether shipping, tax, tips, fees, credits, chargebacks, and disputes are in scope. A query that excludes them can still be correct for a narrow prompt. A query that silently mixes them cannot be explained.
Why does the obvious join duplicate money?
Suppose order O1 has two payment rows and two refund rows. This join creates four combinations:
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
LEFT JOIN refunds r ON r.payment_id = p.payment_id
If each payment has one refund, the join may be safe for that fixture. If one payment has two refunds, its amount appears twice. If refunds are joined at order level, every payment can pair with every refund. SUM(o.order_total_minor) is also repeated once per joined row.
SUM(DISTINCT amount) is not a repair. Two legitimate $25 payments have the same value but different identities; DISTINCT collapses one. The fix is structural: aggregate each source to the target grain before combining it.
Say the invariant aloud: “Each CTE returns at most one row per order and currency.” That makes the final join auditable.
How do you write the core reconciliation query?
This PostgreSQL-style example uses an end-exclusive cutoff. It counts only succeeded payments and succeeded refunds by the time the report closed. Refunds inherit the order through their payment.
WITH payment_by_order AS (
SELECT p.order_id,
p.currency,
SUM(p.amount_minor) FILTER (
WHERE p.status = 'succeeded'
) AS paid_minor,
COUNT(*) FILTER (
WHERE p.status = 'succeeded'
) AS successful_payment_count
FROM payments AS p
WHERE p.created_at < :as_of_exclusive
GROUP BY p.order_id, p.currency
),
refund_by_order AS (
SELECT p.order_id,
r.currency,
SUM(r.amount_minor) FILTER (
WHERE r.status = 'succeeded'
) AS refunded_minor,
COUNT(*) FILTER (
WHERE r.status = 'succeeded'
) AS successful_refund_count
FROM refunds AS r
JOIN payments AS p ON p.payment_id = r.payment_id
WHERE r.created_at < :as_of_exclusive
GROUP BY p.order_id, r.currency
)
SELECT o.order_id,
o.currency,
o.order_total_minor,
COALESCE(p.paid_minor, 0) AS paid_minor,
COALESCE(r.refunded_minor, 0) AS refunded_minor,
COALESCE(p.paid_minor, 0)
- COALESCE(r.refunded_minor, 0) AS retained_minor,
COALESCE(p.paid_minor, 0)
- o.order_total_minor AS gross_difference_minor,
CASE
WHEN p.order_id IS NULL THEN 'missing_payment'
WHEN COALESCE(r.refunded_minor, 0) > COALESCE(p.paid_minor, 0)
THEN 'refund_exceeds_payment'
WHEN p.paid_minor < o.order_total_minor THEN 'underpaid'
WHEN p.paid_minor > o.order_total_minor THEN 'overpaid'
ELSE 'gross_match'
END AS reconciliation_status
FROM orders AS o
LEFT JOIN payment_by_order AS p
ON p.order_id = o.order_id
AND p.currency = o.currency
LEFT JOIN refund_by_order AS r
ON r.order_id = o.order_id
AND r.currency = o.currency
WHERE o.created_at < :as_of_exclusive
ORDER BY o.order_id;
The currency predicates in the joins prevent unlike units from being added. However, they also make a mismatched currency look absent. In production, add separate exception queries for records whose order exists but currency differs. Do not add a currency_mismatch branch to this CASE: after these join predicates, that branch would be unreachable. An interview answer earns trust by naming what its main query cannot surface.
PostgreSQL documents the FILTER clause for selecting rows within an aggregate. In an engine without this syntax, use SUM(CASE WHEN status = 'succeeded' THEN amount_minor ELSE 0 END). PostgreSQL aggregate expressions.
Which statuses and times belong in the report?
“Created before midnight” is not the same as “succeeded before midnight.” A payment created on Monday and completed Tuesday belongs to different snapshots depending on the event time chosen. State whether the report is an as-of operational view, an event-flow audit, or a finalized accounting period.
Use one time zone and an end-exclusive interval such as [2026-09-01 00:00 UTC, 2026-10-01 00:00 UTC). End-exclusive boundaries compose cleanly and avoid fractional-second guesses. Keep both event time and ingestion time if late events matter.
Stripe officially documents that a charge can have more than one refund, the total cannot exceed the original charge, and a refund may be partial. It also documents statuses including pending, succeeded, failed, requires action, and canceled. A created refund is therefore not automatically retained-cash movement. Stripe refund documentation.
Do not overwrite history with only the latest webhook payload. Stripe warns that event ordering is not guaranteed and that an endpoint can receive duplicate events. Store provider event identifiers, apply idempotent processing, and reconcile against current source objects or balance transactions rather than counting webhook deliveries. Stripe webhook best practices.
How should currencies and amounts be handled?
Keep currencies separate unless the prompt supplies an exchange-rate table and a defined rate policy. 100 USD + 100 JPY is not 200 of anything. A conversion query must specify the rate date, source, direction, rounding rule, and whether refunds reuse the original transaction's rate.
Stripe's API expresses amounts as integers in a currency's minor unit, with documented exceptions for zero-decimal and special-case currencies. Do not divide every amount by 100 inside the reconciliation logic. Keep integers for equality checks, then format for display using currency metadata. Stripe currency documentation.
This also prevents floating-point noise. A difference of one minor unit can be real; a difference of 0.0000001 created by binary arithmetic is usually an implementation artifact. If the source uses decimal amounts, choose explicit precision and rounding at the conversion boundary.
How do you find records missing from either side?
An order-led query finds orders without payments, but it cannot reveal a successful payment whose order_id has no order. Add an exception query from the other direction or use a full outer join between already aggregated sources.
SELECT COALESCE(o.order_id, p.order_id) AS order_id,
CASE
WHEN o.order_id IS NULL THEN 'orphan_payment'
WHEN p.order_id IS NULL THEN 'missing_payment'
ELSE 'present_both'
END AS presence_status
FROM orders AS o
FULL OUTER JOIN payment_by_order AS p
ON p.order_id = o.order_id
AND p.currency = o.currency;
MySQL does not support FULL OUTER JOIN directly. Emulate it with a left join plus the unmatched rows from the reverse left join, combined with UNION ALL. Use UNION ALL because the anti-join predicate already makes the two branches disjoint.
Also retain source identifiers in a drill-down table. A finance or support teammate needs to move from underpaid to the contributing payment IDs without reverse-engineering an aggregate.
What mismatch categories make the result useful?
Avoid one overloaded flag called mismatch. Use categories tied to next actions:
| Category | Likely investigation |
|---|---|
missing_payment | checkout failure, late event, or broken order key |
orphan_payment | deleted/missing order, wrong key, or ingestion gap |
underpaid | partial payment, failed attempt, or discount omission |
overpaid | duplicate capture or order total revision |
refund_pending | expected timing difference; monitor rather than force-match |
refund_exceeds_payment | duplicated refund, currency error, or corrupt linkage |
currency_mismatch | wrong ISO code or cross-currency modeling gap |
duplicate_source_id | ingestion replay or non-idempotent processing |
These are hypotheses, not conclusions. The SQL should expose evidence such as counts, source IDs, statuses, and timestamps. Do not label a payment “fraud” because it is duplicated; reconciliation detects inconsistency, not intent.
What fixtures prove the query is correct?
Use a compact dataset with exact expected outputs:
- One order paid once with no refund: gross and retained amounts match.
- One order paid in two installments: payment count is two, total still matches.
- One payment with two successful partial refunds: payment amount must not double.
- One succeeded refund plus one failed refund: subtract only the succeeded amount.
- One underpayment and one overpayment: preserve the sign of the difference.
- One order with no payment and one payment with no order: both directions appear.
- One refund arriving after the cutoff: absent from the earlier snapshot, present later.
- Two identical amounts with different IDs: both remain; no value-based deduplication.
Then assert invariants. A refund total cannot be negative. A successful refund total should not exceed its payment unless the business model explicitly permits a separate credit. Counts before and after each aggregation should match the declared grain. Re-running the same source batch should not change totals.
How do you explain this in an interview?
Use a four-part answer. First, define the grain and equations. Second, state status, cutoff, currency, and included-component rules. Third, pre-aggregate each one-to-many table before joining. Fourth, show reason-coded exceptions and tests.
Start with the transaction-data-quality exercise, then rebuild its checks around one row per order and currency. For every mismatch, say which source record you would inspect next. That turns a correct query into an operational answer.