Instacart operations wants a quick store-level summary of currently open shopper support issues. Write a SQL query to pivot issue counts by store.
store_name for records where issue_status = 'open'.total_open_issues.late_pick as late_pick_issuesout_of_stock as out_of_stock_issuessubstitution_needed as substitution_needed_issuestotal_open_issues descending, then store_name ascending.shopper_support_issues
| Column | Type | Description |
|---|---|---|
| issue_id | INT | Unique issue record ID |
| store_name | VARCHAR(100) | Partner store name |
| issue_type | VARCHAR(50) | Type of shopper issue |
| issue_status | VARCHAR(20) | Current issue status |
| reported_date | DATE | Date the issue was reported |
| affected_orders | INT | Number of affected orders |
| issue_id | store_name | issue_type | issue_status | reported_date | affected_orders |
|---|---|---|---|---|---|
| 1 | Costco SoMa | late_pick | open | 2024-05-03 | 4 |
| 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 |
| 8 | Wegmans Berkeley | substitution_needed | open | 2024-05-03 | NULL |
| store_name | total_open_issues | late_pick_issues | out_of_stock_issues | substitution_needed_issues |
|---|---|---|---|---|
| Safeway Mission | 4 | 1 | 1 | 1 |
| Costco SoMa | 3 | 2 | 1 | 0 |
| Sprouts Daly City | 2 | 0 | 1 | 1 |
| Wegmans Berkeley | 1 | 0 | 0 | 1 |
| Column | Type | Description |
|---|---|---|
| issue_idPK | 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 |
{"shopper_support_issues":[[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],[7,"Costco SoMa","late_pick","closed","2024-05-01",1],[8,"Wegmans Berkeley","substitution_needed","open","2024-Output[["Safeway Mission",4,1,1,1],["Costco SoMa",3,2,1,0],["Sprouts Daly City",2,0,1,1],["Wegmans Berkeley",1,0,0,1]]