Myntra: Insider Points Earned on Each Delivered Order
Insider members earn points on every delivered order, and the rate depends on their tier. The loyalty team needs the points to credit for each order.
- Difficulty: Easy
- Topics: Conditional Logic, Joins
- Dialect: MySQL
- Problem: #803
Problem statement
Insider members earn points on every delivered order, and the rate depends on their tier. The loyalty team needs the points to credit for each order.
A shopper earns points for every full ₹100 of order_value: 2 points per ₹100 at insider, 3 at select, 4 at elite, 5 at icon, and 0 for none. For every order with status = 'delivered', return order_id, name and points. An order under ₹100 earns 0. Return the rows in any order.
A practice scenario set at Myntra: the tables and data are invented, and this is not a question from, or endorsed by, Myntra.
Tables
Table: Shopper
| Column | Type |
|---|---|
| user_id | int |
| name | varchar |
| insider_tier | enum(none, insider, select, elite, icon) |
Primary key: user_id.
One row per account and its current Insider tier.
Table: ShopOrder
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| order_value | int |
| status | enum(delivered, returned, cancelled) |
Primary key: order_id.
One row per order; order_value is in whole rupees.
Examples
Example 1
Shopper
| user_id | name | insider_tier |
|---|---|---|
| 1 | Aarav | elite |
| 2 | Diya | none |
| 3 | Kabir | icon |
| 4 | Meera | insider |
| 5 | Rohan | select |
ShopOrder
| order_id | user_id | order_value | status |
|---|---|---|---|
| 101 | 1 | 2599 | delivered |
| 102 | 2 | 4999 | delivered |
| 103 | 3 | 99 | delivered |
| 104 | 4 | 1200 | delivered |
| 105 | 5 | 3450 | returned |
| 106 | 5 | 800 | delivered |
| 107 | 3 | 1999 | delivered |
Output
| order_id | name | points |
|---|---|---|
| 101 | Aarav | 100 |
| 102 | Diya | 0 |
| 103 | Kabir | 0 |
| 104 | Meera | 24 |
| 106 | Rohan | 24 |
| 107 | Kabir | 95 |
How to solve Myntra: Insider Points Earned on Each Delivered Order
Points are a product of two numbers: how many full hundreds the order is worth, and the tier's rate per hundred. The first is FLOOR(order_value / 100) — in MySQL / is exact division, so 2599 / 100 is 25.99 and FLOOR brings it down to 25. order_value DIV 100, integer division, gives the same 25 directly and is what the alternative uses. An order of ₹99 has no full hundred and earns 0 whatever the tier.
The rate comes from the shopper's tier, so the orders are joined to Shopper and a CASE maps each tier to 2, 3, 4 or 5, with ELSE 0 covering none. The alternative keeps the rates as a small table built with UNION ALL and LEFT JOINs it, so a tier missing from it (here none) comes back NULL and COALESCE turns that into 0 — the same idea as a rates table you would keep in a real warehouse.
Only delivered orders are credited; the WHERE clause drops returned and cancelled ones. The query is one pass over the orders with a key lookup per shopper.
Reference solution (MySQL)
SELECT o.order_id, s.name,
FLOOR(o.order_value / 100) * CASE s.insider_tier
WHEN 'insider' THEN 2 WHEN 'select' THEN 3 WHEN 'elite' THEN 4 WHEN 'icon' THEN 5 ELSE 0
END AS points
FROM ShopOrder o
JOIN Shopper s ON s.user_id = o.user_id
WHERE o.status = 'delivered'Another way
SELECT o.order_id, s.name, (o.order_value DIV 100) * COALESCE(r.rate, 0) AS points
FROM ShopOrder o
JOIN Shopper s ON s.user_id = o.user_id
LEFT JOIN (SELECT 'insider' AS tier, 2 AS rate UNION ALL SELECT 'select', 3
UNION ALL SELECT 'elite', 4 UNION ALL SELECT 'icon', 5) r ON r.tier = s.insider_tier
WHERE o.status = 'delivered'← Myntra: Wishlisted Styles the Shopper Has Not Bought Yet · Myntra: Return Requests Raised After the Category's Window →