Task
Instacart's Operations team wants a quick view similar to an Excel pivot table to decide which stores need support. Write a SQL query to summarize open picker issues by store_name and issue_type.
Requirements
- Return each
store_name with the total number of open issues.
- Break that total into separate counts for
late_pick, out_of_stock, and substitution_needed.
- Only include rows where
issue_status = 'open'.
- Order the results by total open issues descending, then
store_name ascending.
Table Definition
shopper_support_issues
| column_name | type | description |
|---|
| issue_id | INT | Unique issue record ID |
| store_name | VARCHAR(100) | Instacart partner store name |
| issue_type | VARCHAR(50) | Type of picker issue |
| issue_status | VARCHAR(20) | Current issue status |
| reported_date | DATE | Date the issue was reported |
| affected_orders | INT | Number of orders affected |
Sample Data
| issue_id | store_name | issue_type | issue_status | reported_date | affected_orders |
|---|
| 1 | Costco SoMa | late_pick | open | 2024-05-03 | 4 |
| 2 | Sprouts Daly City | out_of_stock | resolved | 2024-05-01 | 2 |
| 3 | Safeway Mission | substitution_needed | open | 2024-05-02 | 1 |
| 4 | Costco SoMa | out_of_stock | open | 2024-05-02 | 3 |
| 5 | Safeway Mission | late_pick | open | 2024-05-01 | 2 |
| 6 | Sprouts Daly City | substitution_needed | open | 2024-05-04 | 0 |
Expected Output
| store_name | total_open_issues | late_pick_issues | out_of_stock_issues | substitution_needed_issues |
|---|
| Costco SoMa | 3 | 2 | 1 | 0 |
| Safeway Mission | 3 | 1 | 1 | 1 |
| Sprouts Daly City | 2 | 0 | 1 | 1 |
| Wegmans Berkeley | 1 | 0 | 0 | 1 |
