Data analyst interview: reconcile two conflicting revenue numbers

When two reports show conflicting “revenue,” first compare their definitions, populations, grain, time boundaries and source versions. Do not assume that the larger number is wrong or that the finance team's label identifies the calculation by itself.

This hypothetical interview uses synthetic completed-order amounts. It is an analytical reconciliation exercise, not accounting advice or a report from an actual employer assessment.

Clarify what each report claims to measure

Suppose Report A shows 104,000 paise and Report B shows 171,000 for January. Both say they include completed orders. Ask whether the source is one row per order or one row per item, whether customer joins affect eligibility and whether refunds or discounts are handled consistently.

The current contract uses each supplied order total once, includes unmatched customers and does not subtract the separate discount field again. It does not claim recognized revenue or cash collection.

This definition gives you a reference against which to investigate. Without it, reconciling two numbers can become an argument about labels rather than a testable comparison.

Reproduce both calculations

import importlib.util
from pathlib import Path
from calculator import calculate

path = Path('../commerce-sql/build_and_verify.py')
spec = importlib.util.spec_from_file_location('interview_commerce',path)
module = importlib.util.module_from_spec(spec)
spec.loader.exec_module(module)
db = module.database()
try:
    correct = db.execute("SELECT COUNT(*),SUM(order_total_paise) FROM orders WHERE status='completed'").fetchone()
    fanout = db.execute("SELECT COUNT(*),SUM(o.order_total_paise) FROM orders o JOIN order_items i ON i.order_id=o.order_id WHERE o.status='completed'").fetchone()
    distinct = db.execute("SELECT SUM(DISTINCT order_total_paise) FROM orders WHERE status='completed'").fetchone()[0]
    inner = db.execute("SELECT SUM(o.order_total_paise) FROM orders o JOIN customers c ON c.customer_id=o.customer_id WHERE o.status='completed'").fetchone()[0]
finally:
    db.close()
assert correct==(8,104000) and fanout==(12,171000)
assert distinct==82000 and inner==95000
assert calculate()['value']==104000
print({'reference':correct,'joined_header_result':fanout,
       'distinct_amount_result':distinct,'inner_customer_result':inner})

All completed orders in this small fixture are in January, so the diagnostic queries above reproduce the January figures. A general report must still include explicit period filters; do not carry that fixture shortcut into an unrestricted dataset.

Explain the difference at row level

Report B repeats order-header amounts across twelve joined item rows. The problem is fan-out: the aggregation occurs after a one-to-many join without preserving the order grain.

SUM(DISTINCT amount) produces 82,000, but it is not the solution. Different orders legitimately share amounts, so deduplicating values removes valid contributions. Aggregate at the intended entity grain or use a suitable item-level measure under its own contract.

An inner customer join produces a different wrong result, 95,000, because it drops the unmatched order O1009. That is an eligibility change, not a monetary adjustment.

Present a reconciliation rather than a verdict

Explain the reference amount, the transformation that created each discrepancy and the corrective action. Preserve the relevant row IDs and counts so another analyst can verify the diagnosis.

If two reports intentionally use different valid definitions, report the bridge instead of forcing them to match. For example, an order amount and a dated cash collection measure can legitimately differ. This fixture's refund ledger has no timestamps, so it cannot establish a January cash-flow bridge.

Close with a prevention check

Add a unique-key check at the order source, a row-count or grain check after joins, and a reconciliation between eligible order IDs and the report. Document the metric contract beside the output.

In the interview, a concise response is stronger when it includes the investigation sequence and an actual diagnosed failure. Avoid claiming that a generic “remove duplicates” step resolves every mismatch.

Exercise: explain how you would investigate a 9,000-paise difference before seeing the SQL. Identify the source record and join behavior that would confirm the unmatched-customer hypothesis in this fixture.

NeuraPath's Data Analytics with Generative AI course develops reconciliation across SQL, spreadsheets and BI. The transferable skill is tracing disagreement to a definition or transformation that can be inspected and corrected.

Continue learning

This article is part of the Analyst career preparation and interviews sequence. Use the neighbouring tasks when you need the prerequisite or the next application.

• Review the prerequisite or neighbouring task in Explain a dashboard project in a five-minute interview.
• Continue with Data analyst interview: diagnose a suspicious conversion uplift.