Identify invoice-level discrepancies across Garmin invoice, payment, and customer tables. Return only problematic invoices and assign one issue label using this priority: invalid_customer > missing_payment > duplicate_payment > amount_mismatch.
| Column | Type | Description |
|---|---|---|
| customer_id | ||
| customer_name | ||
| status |
| Column | Type | Description |
|---|---|---|
| invoice_id | ||
| customer_id | ||
| invoice_date | ||
| invoice_amount |
| Column | Type | Description |
|---|---|---|
| payment_id | ||
| invoice_id | ||
| payment_date | ||
| payment_amount |
Joining fact and dimension tablesAggregating payments to invoice levelUsing a CTE for reconciliation logicApplying CASE logic with issue priority