Myntra: Insider Points Earned on Each Delivered Order

Easy
803.

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.

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

ColumnTypeValues
user_idint
namevarchar
insider_tierenumnone, insider, select, elite, icon

Primary key: user_id.

One row per account and its current Insider tier.

Table: ShopOrder

ColumnTypeValues
order_idint
user_idint
order_valueint
statusenumdelivered, returned, cancelled

Primary key: order_id.

One row per order; order_value is in whole rupees.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Shopper

user_idnameinsider_tier
1Aaravelite
2Diyanone
3Kabiricon
4Meerainsider
5Rohanselect

ShopOrder

order_iduser_idorder_valuestatus
10112599delivered
10224999delivered
103399delivered
10441200delivered
10553450returned
1065800delivered
10731999delivered

Output

order_idnamepoints
101Aarav100
102Diya0
103Kabir0
104Meera24
106Rohan24
107Kabir95
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
user_idint
namevarchar
insider_tierenum(none, insider, select, elite, icon)

Primary key: user_id.

One row per account and its current Insider tier.

Table: ShopOrder

ColumnType
order_idint
user_idint
order_valueint
statusenum(delivered, returned, cancelled)

Primary key: order_id.

One row per order; order_value is in whole rupees.

Examples

Example 1

Shopper

user_idnameinsider_tier
1Aaravelite
2Diyanone
3Kabiricon
4Meerainsider
5Rohanselect

ShopOrder

order_iduser_idorder_valuestatus
10112599delivered
10224999delivered
103399delivered
10441200delivered
10553450returned
1065800delivered
10731999delivered

Output

order_idnamepoints
101Aarav100
102Diya0
103Kabir0
104Meera24
106Rohan24
107Kabir95

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 →