You are given internal order data, partner-reported totals, and discrepancy reason codes. Write a PostgreSQL query that helps investigate a recurring data discrepancy by returning, for each partner and day, the internal order count, internal revenue, partner-reported revenue, the revenue delta, and a discrepancy status that labels rows as match, underreported, overreported, or missing_report. Only include days where the partner reported data or the internal system has orders.
| Column | Type | Description |
|---|---|---|
| partner_idPK | INT | Primary key for the retail partner |
| partner_name | VARCHAR(100) | Partner display name |
| Column | Type | Description |
|---|---|---|
| report_idPK | INT | Primary key for the partner report row |
| partner_id | INT | Foreign key to retail_partners.partner_id |
| report_date | DATE | Date covered by the report |
| reported_revenue | NUMERIC(12,2) | Revenue reported by the partner |
| reported_order_count | INT | Order count reported by the partner |
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Primary key for the internal order |
| partner_id | INT | Foreign key to retail_partners.partner_id |
| order_date | DATE | Order date in the internal system |
| order_amount | NUMERIC(12,2) | Revenue from the internal system |
| Column | Type | Description |
|---|---|---|
| reason_idPK | INT | Primary key for the discrepancy reason |
| partner_id | INT | Foreign key to retail_partners.partner_id |
| reason_date | DATE | Date the reason was logged |
| reason_code | VARCHAR(50) | Reason code such as late_feed, mapping_error, or promo_mismatch |