Analyze Enrollment and Billing Discrepancies | Dataford Interview Questions

Explain how you would approach analyzing enrollment and billing data to identify discrepancies in monthly customer invoices for an Infosys client.

Write a SQL query that compares monthly enrolled amounts to monthly billed amounts and flags customers where the two totals do not match. Focus on invoice-level discrepancies for each customer and month.

Output

One row per customer and invoice month where a discrepancy exists
Columns: customer_id, invoice_month, enrolled_amount, billed_amount, difference
Return only rows where enrolled_amount and billed_amount are not equal
Sort by invoice_month, then customer_id

Schema

enrollment

Column	Type	Description
enrollment_idPK	INT	Unique enrollment record identifier
customer_id	INT	Customer identifier
enrollment_date	DATE	Date the customer was enrolled
enrolled_amount	DECIMAL(10,2)	Amount recognized from enrollment
enrollment_status	VARCHAR(20)	Enrollment status such as active or pending

billing

Column	Type	Description
billing_idPK	INT	Unique billing record identifier
customer_id	INT	Customer identifier
billing_date	DATE	Date the invoice was billed
billed_amount	DECIMAL(10,2)	Amount billed on the invoice
invoice_status	VARCHAR(20)	Invoice status such as posted, void, or pending

Your question is Analyze Enrollment and Billing 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