Task
You are given invoice, payment, and customer data from Garmin Business Systems with known discrepancies. Write a SQL query that identifies invoices with data quality or reconciliation issues and classifies each issue as missing_payment, amount_mismatch, duplicate_payment, or invalid_customer. Return one row per problematic invoice with the invoice amount, total applied payment amount, payment count, and issue type.
Use the customer table to validate that the invoice is tied to an active customer record. Treat invoices with no matching payment rows as missing payments, invoices with more than one payment row as duplicate payments, invoices whose summed payments do not equal the invoice amount as amount mismatches, and invoices tied to inactive or missing customers as invalid customers. If an invoice has multiple problems, prioritize invalid_customer, then missing_payment, then duplicate_payment, then amount_mismatch.
Schema
garmin_customers
| column | type | description |
|---|
| customer_id | INT | Customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| status | VARCHAR(20) | Customer status |
garmin_invoices
| column | type | description |
|---|
| invoice_id | INT | Invoice identifier |
| customer_id | INT | Billed customer |
| invoice_date | DATE | Invoice date |
| invoice_amount | DECIMAL(10,2) | Invoice total |
garmin_payments
| column | type | description |
|---|
| payment_id | INT | Payment identifier |
| invoice_id | INT | Related invoice |
| payment_date | DATE | Payment date |
| payment_amount | DECIMAL(10,2) | Applied payment |
Sample data
Representative rows include invoices with no payment, multiple payments, mismatched totals, and inactive or missing customers.
Expected output
| invoice_id | customer_id | invoice_amount | total_payment_amount | payment_count | issue_type |
|---|
| 1002 | 2 | 450.00 | 400.00 | 1 | invalid_customer |
| 1004 | 4 | 300.00 | 300.00 | 2 | duplicate_payment |
| 1005 | 5 | 0.00 | 0.00 | 0 | invalid_customer |
| 1006 | 6 | 275.00 | 0.00 | 0 | missing_payment |
| 1007 | 3 | 150.00 | 140.00 | 1 | amount_mismatch |
