Task
You are given order, return, and seller data. Write a PostgreSQL query that finds sellers with unusually high return rates over the last quarter, where return rate is the number of returned orders divided by total completed orders in that quarter. Return each seller’s ID, seller name, total orders, returned orders, and return rate, and only include sellers whose return rate is above 20%.
Schema
| table | column | type | description |
|---|
| sellers | seller_id | INT | Primary key for each seller |
| sellers | seller_name | VARCHAR(255) | Seller display name |
| orders | order_id | INT | Primary key for each order |
| orders | seller_id | INT | Seller who fulfilled the order |
| orders | order_date | DATE | Order completion date |
| orders | order_status | VARCHAR(50) | Order status such as completed or canceled |
| returns | return_id | INT | Primary key for each return |
| returns | order_id | INT | Order that was returned |
| returns | return_date | DATE | Date the return was created |
Sample data
| seller_id | seller_name |
|---|
| 1 | North Star Finds |
| 2 | Cozy Closet Live |
| 3 | Retro Vault |
| order_id | seller_id | order_date | order_status |
|---|
| 101 | 1 | 2024-03-02 | completed |
| 102 | 1 | 2024-03-18 | completed |
| 103 | 1 | 2024-03-25 | completed |
| 104 | 2 | 2024-03-05 | completed |
| 105 | 2 | 2024-03-20 | completed |
| 106 | 2 | 2024-03-28 | canceled |
| 107 | 3 | 2024-03-10 | completed |
| 108 | 3 | 2024-03-22 | completed |
| return_id | order_id | return_date |
|---|
| 201 | 101 | 2024-03-08 |
| 202 | 102 | 2024-03-21 |
| 203 | 104 | 2024-03-11 |
| 204 | 107 | 2024-03-18 |
| 205 | 108 | 2024-03-30 |
Expected output
| seller_id | seller_name | total_orders | returned_orders | return_rate |
|---|
| 1 | North Star Finds | 3 | 2 | 0.6667 |
| 3 | Retro Vault | 2 | 2 | 1.0000 |
