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%.
Output details
- Treat
DATE '2024-01-01' as today's date.
- Use an inclusive start of
DATE '2024-01-01' and an exclusive end of DATE '2024-04-01'.
- Name and order the output columns
seller_id, seller_name, total_orders, returned_orders, and return_rate.
- Round
return_rate to four decimal places before applying the 0.20 threshold.
- Sort by
return_rate descending, then total_orders descending, then seller_id ascending.
- Count matching return records without deduplicating orders.