Damage share of refunds
mediumDamage share of refunds
Flipkart SQL Interview Question
Damage is the only return reason Flipkart can fix by spending money: better packaging and a different courier lane. The business case for that spend is the share of refunded rupees damage accounts for, not the share of returns. Ten cheap shirts torn in transit matter far less than one television.
Return the category, the money refunded there as total_refund, the part of it caused by damage as damaged_refund, and damage as a percentage of the category total as damaged_share, rounded to 2 decimal places.
Asked of
- Data Analyst
- Business Analyst
- BI Analyst
- Product Analyst
- Analytics Engineer
- Data Engineer
- Data Scientist
flipkart_returnsTable26 rows
| Column Name | Type |
|---|---|
| return_id | BIGINT |
| order_id | BIGINT |
| category | VARCHAR |
| requested_on | DATE |
| reason | VARCHAR |
| refund_amount | DOUBLE |
flipkart_returnsExample Input
| return_id | order_id | category | requested_on | reason | refund_amount |
|---|---|---|---|---|---|
| 3001 | 70011 | Apparel | 2024-08-01 | size issue | 1299 |
| 3002 | 70012 | Apparel | 2024-08-01 | damaged | 2499 |
| 3003 | 70013 | Electronics | 2024-08-02 | damaged | 15999 |
| 3004 | 70014 | Home | 2024-08-02 | not as described | 3499 |
| 3005 | 70015 | Apparel | 2024-08-03 | size issue | 899 |
| 3006 | 70016 | Electronics | 2024-08-03 | not as described | 7499 |
| 3007 | 70017 | Apparel | 2024-08-04 | size issue | 1599 |
| 3008 | 70018 | Home | 2024-08-04 | damaged | 2899 |
Example Output
| category | total_refund | damaged_refund | damaged_share |
|---|---|---|---|
| Apparel | 6296 | 2499 | 39.69 |
| Electronics | 23498 | 15999 | 68.09 |
| Home | 6398 | 2899 | 45.31 |
Explanation
Apparel refunded 6296 in total, of which 2499 was damage, a share of 39.69. Electronics refunded far more overall and damage accounts for 68.09 of it, which is the point: one broken television outweighs several torn shirts.
The example above is a small slice of the data. Your query runs against the full tables.
Company
Flipkart
Difficulty
medium
Topic
reshaping
Language
SQL
Your query
Starting the SQL engine...