Resolving Report Discrepancies | Dataford Interview Questions

How would you handle a situation when you discovered a discrepancy on a report?

Use the provided report and transaction tables to identify periods where the reported total does not agree with the transaction-derived total, including missing report periods and missing source transactions.

Output

One row per discrepancy, with customer_id, report_month, reported_total, calculated_total, variance, and discrepancy_status.
Include amount mismatches, missing reports, missing source transactions, and NULL reported totals. Exclude matching totals.
Order by report_month ascending, then customer_id ascending.

Schema

report_totals

Column	Type	Description
customer_idPK	INT	Customer identifier reported in the monthly report
report_monthPK	DATE	First day of the reporting month
reported_total	NUMERIC(12,2)	Total amount recorded in the report

source_transactions

Column	Type	Description
transaction_idPK	INT	Unique source transaction identifier
customer_id	INT	Customer associated with the transaction
transaction_date	DATE	Date on which the transaction occurred
amount	NUMERIC(12,2)	Transaction amount used to calculate the expected report total

Your question is Resolving Report Discrepancies. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem