Refund Rate
ASKED IN INTERVIEWShopify
Problem Statement
Shopify Risk Management wants to identify stores with unusually high refund rates.
Write an SQL query to find the refund rate for each store.
Rules:
- refund_rate = (Orders with status "refunded" / Total Orders) * 100.
- Round the refund_rate to 2 decimal places.
- If a store has no orders at all, the refund_rate should be 0.00.
- Output columns: store_name, refund_rate.
- Results must be sorted by refund_rate in descending order, then store_name in ascending order.
Tests your understanding of
Conditional Aggregation, Joins and Ratios
Input Tables
Stores
| store_id(INTEGER) | store_name(VARCHAR) |
|---|---|
| 1 | Urban Threads |
| 2 | Tech Haven |
| 3 | Green Sprout |
Orders
| order_id(INTEGER) | store_id(INTEGER) | status(VARCHAR) |
|---|---|---|
| 1 | 1 | refunded |
| 2 | 1 | completed |
| 3 | 1 | completed |
| 4 | 2 | refunded |
| 5 | 2 | completed |
Expected Output
| store_name(VARCHAR) | refund_rate(DECIMAL) |
|---|---|
| Tech Haven | 50 |
| Urban Threads | 33.33 |
| Green Sprout | 0 |
Tags
MediumASKED IN INTERVIEWConditional AggregationJoinsRatios
10-15 min
71%