Task
Whatnot's Operations team wants to understand the most common reasons buyers file shipping claims in the marketplace. Write a SQL query to identify the most frequent shipping claim reasons from the shipping_claims table.
Requirements
- Return each non-null
claim_reason and the number of claims for that reason.
- Order the results by claim count descending, then by
claim_reason ascending.
Table Definition
| column_name | type | description |
|---|
| claim_id | INT | Primary key for each shipping claim |
| order_id | INT | Whatnot order tied to the claim |
| claim_reason | VARCHAR(100) | Reason selected for the shipping claim |
| claim_status | VARCHAR(30) | Current status of the claim |
| claim_created_at | DATE | Date the claim was created |
Sample Data
| claim_id | order_id | claim_reason | claim_status | claim_created_at |
|---|
| 101 | 5001 | Item not received | open | 2024-03-05 |
| 102 | 5002 | Damaged in transit | resolved | 2024-03-02 |
| 103 | 5003 | Item not received | open | 2024-03-01 |
| 104 | 5004 | Wrong item received | denied | 2024-03-06 |
| 105 | 5005 | Damaged in transit | open | 2024-03-04 |
| 106 | 5006 | Missing items | resolved | 2024-03-03 |
| 107 | 5007 | Item not received | resolved | 2024-03-07 |
| 108 | 5008 | | open | 2024-03-08 |
| 109 | 5009 | NULL | open | 2024-03-09 |
| 110 | 5010 | Damaged in transit | denied | 2024-03-10 |
Expected Output
| claim_reason | claim_count |
|---|
| Damaged in transit | 3 |
| Item not received | 3 |
| Missing items | 2 |
| Wrong item received | 2 |
