Resolve Conflicting Revenue Across Sources

Context

You are comparing revenue reported by two systems, and the numbers do not match. In finance workflows, this usually means you need to identify the source of truth, quantify the variance, and isolate the records driving the mismatch.

Core question

Explain how you would investigate two data sources that report conflicting revenue numbers. In your answer, describe how you would compare the datasets, handle missing or duplicate rows, decide which source should be trusted for reporting, and surface the records that explain the difference.

Scope guidance

Keep your answer focused on SQL and data manipulation. The interviewer expects you to discuss reconciliation logic, join strategy, aggregation checks, null handling, and how you would structure a query or workflow to expose the discrepancy clearly.

Revenue Reconciliation

Reconciliation is the process of comparing two datasets that should represent the same business metric and identifying where they diverge. In SQL, this usually means aggregating each source to the same grain, joining on shared keys, and calculating variance at the total and row level.

SELECT a.period, a.revenue AS source_a_revenue, b.revenue AS source_b_revenue, a.revenue - b.revenue AS variance
FROM source_a a
FULL OUTER JOIN source_b b
  ON a.period = b.period;

Source of Truth

When sources conflict, you need a rule for which system is authoritative for reporting. That decision may depend on completeness, timeliness, auditability, or whether one source is downstream and already cleaned.

Null and Missing Data Handling

Missing rows can look like zero revenue if they are not handled carefully. Using LEFT JOIN, FULL OUTER JOIN, and COALESCE helps you distinguish true zeros from absent records and avoid hiding discrepancies.

SELECT COALESCE(a.period, b.period) AS period,
       COALESCE(a.revenue, 0) AS source_a_revenue,
       COALESCE(b.revenue, 0) AS source_b_revenue
FROM source_a a
FULL OUTER JOIN source_b b
  ON a.period = b.period;

Duplicate Detection

Conflicting totals are often caused by duplicate rows in one source rather than a true business difference. Before comparing totals, you should check whether either source has duplicate keys or repeated transactions that inflate revenue.

SELECT transaction_id, COUNT(*)
FROM revenue_source
GROUP BY transaction_id
HAVING COUNT(*) > 1;