You are given retailer inventory snapshots and the corresponding inventory currently shown in the Instacart app. Write a PostgreSQL query that helps you troubleshoot discrepancies by returning the SKUs where the two sources disagree for the latest snapshot date, along with the prior app state and the mismatch type.
Use the latest snapshot_date available in the retailer file, compare it to the matching app inventory on that date, and surface only rows where the retailer and app quantities differ or where a SKU exists in one source but not the other. Include the retailer quantity, app quantity, previous app quantity, and a discrepancy_reason that distinguishes quantity mismatches from missing SKUs. Order the output so the most recently changed items appear first.
| Column | Type | Description |
|---|---|---|
| file_idPK | INT | Primary key for each retailer file row |
| retailer_id | INT | Retailer identifier |
| snapshot_date | DATE | File date for the inventory snapshot |
| sku | VARCHAR(50) | Retailer SKU |
| file_qty | INT | Quantity reported in the retailer file |
| Column | Type | Description |
|---|---|---|
| app_idPK | INT | Primary key for each app inventory row |
| retailer_id | INT | Retailer identifier |
| snapshot_date | DATE | Date shown in the Instacart app |
| sku | VARCHAR(50) | SKU shown in the app |
| app_qty | INT | Quantity shown in the app |
| updated_at | TIMESTAMP | Time the app quantity was last updated |