Myntra: Orders Placed in the EORS Insider Early-Access Hours
Before the End of Reason Sale opens to everyone at midnight, Insider members get four hours of early access.
- Difficulty: Easy
- Topics: Joins, Dates
- Dialect: MySQL
- Problem: #798
Problem statement
Before the End of Reason Sale opens to everyone at midnight, Insider members get four hours of early access. The loyalty team wants the orders that actually used that window.
Return every order placed from 2024-05-31 20:00:00 inclusive up to, but not including, 2024-06-01 00:00:00 by a shopper whose insider_tier is anything other than none, and whose status is not cancelled. Return the columns order_id, name and insider_tier, 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. none means the shopper is not in the Insider programme.
Table: ShopOrder
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| placed_at | datetime |
| order_value | int |
| status | enum(placed, delivered, cancelled) |
Primary key: order_id.
One row per order; user_id always names a shopper.
Examples
Example 1
Shopper
| user_id | name | insider_tier |
|---|---|---|
| 1 | Aarav | elite |
| 2 | Diya | none |
| 3 | Kabir | insider |
| 4 | Meera | icon |
ShopOrder
| order_id | user_id | placed_at | order_value | status |
|---|---|---|---|---|
| 1001 | 1 | 2024-05-31 20:00:00 | 2499 | placed |
| 1002 | 2 | 2024-05-31 21:15:00 | 1899 | placed |
| 1003 | 3 | 2024-05-31 23:59:59 | 999 | delivered |
| 1004 | 4 | 2024-06-01 00:00:00 | 3499 | placed |
| 1005 | 1 | 2024-05-31 19:59:59 | 1299 | placed |
| 1006 | 4 | 2024-05-31 22:40:00 | 5999 | cancelled |
| 1007 | 4 | 2024-05-31 22:45:00 | 5799 | placed |
Output
| order_id | name | insider_tier |
|---|---|---|
| 1001 | Aarav | elite |
| 1003 | Kabir | insider |
| 1007 | Meera | icon |
How to solve Myntra: Orders Placed in the EORS Insider Early-Access Hours
Each order is either in the early-access window or not, so this is a join followed by a filter. The tier is a property of the shopper and the timestamp a property of the order, so ShopOrder is joined to Shopper on user_id and four conditions are applied together: the tier is not none, the order was not cancelled, and the timestamp lies in the window.
The window is half-open: placed_at >= '2024-05-31 20:00:00' keeps an order placed at exactly eight o'clock, and placed_at < '2024-06-01 00:00:00' drops one placed at midnight, which is already the public sale. Writing BETWEEN ... AND '2024-06-01 00:00:00' would wrongly keep midnight; writing <= '2024-05-31 23:59:59' happens to work here because times are stored to the second, but the half-open form is the habit that survives finer timestamps.
The alternative reads the same window as "the date is 31 May and the hour is 20 or later" and lists the allowed values with IN. Both are a single pass over the orders with a primary-key lookup per shopper.
Reference solution (MySQL)
SELECT o.order_id, s.name, s.insider_tier
FROM ShopOrder o
JOIN Shopper s ON s.user_id = o.user_id
WHERE s.insider_tier <> 'none'
AND o.status <> 'cancelled'
AND o.placed_at >= '2024-05-31 20:00:00'
AND o.placed_at < '2024-06-01 00:00:00'Another way
SELECT o.order_id, s.name, s.insider_tier
FROM ShopOrder o, Shopper s
WHERE s.user_id = o.user_id
AND s.insider_tier IN ('insider', 'select', 'elite', 'icon')
AND o.status IN ('placed', 'delivered')
AND CAST(o.placed_at AS DATE) = '2024-05-31'
AND HOUR(o.placed_at) >= 20← Microsoft: Game Pass Win-Back Rate by Cancellation Month · Myntra: Live Styles With No Approved Catalogue Image →