Your question is Investigate Retail Partner Data Discrepancies. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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 |