Myntra: Orders Placed in the EORS Insider Early-Access Hours

Easy
798.

Myntra: Orders Placed in the EORS Insider Early-Access Hours

EasyJoinsDates

MySQL

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

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

Primary key: user_id.

One row per account. none means the shopper is not in the Insider programme.

Table: ShopOrder

ColumnTypeValues
order_idint
user_idint
placed_atdatetime
order_valueint
statusenumplaced, delivered, cancelled

Primary key: order_id.

One row per order; user_id always names a shopper.

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
3Kabirinsider
4Meeraicon

ShopOrder

order_iduser_idplaced_atorder_valuestatus
100112024-05-31 20:00:002499placed
100222024-05-31 21:15:001899placed
100332024-05-31 23:59:59999delivered
100442024-06-01 00:00:003499placed
100512024-05-31 19:59:591299placed
100642024-05-31 22:40:005999cancelled
100742024-05-31 22:45:005799placed

Output

order_idnameinsider_tier
1001Aaravelite
1003Kabirinsider
1007Meeraicon
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: 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

ColumnType
user_idint
namevarchar
insider_tierenum(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

ColumnType
order_idint
user_idint
placed_atdatetime
order_valueint
statusenum(placed, delivered, cancelled)

Primary key: order_id.

One row per order; user_id always names a shopper.

Examples

Example 1

Shopper

user_idnameinsider_tier
1Aaravelite
2Diyanone
3Kabirinsider
4Meeraicon

ShopOrder

order_iduser_idplaced_atorder_valuestatus
100112024-05-31 20:00:002499placed
100222024-05-31 21:15:001899placed
100332024-05-31 23:59:59999delivered
100442024-06-01 00:00:003499placed
100512024-05-31 19:59:591299placed
100642024-05-31 22:40:005999cancelled
100742024-05-31 22:45:005799placed

Output

order_idnameinsider_tier
1001Aaravelite
1003Kabirinsider
1007Meeraicon

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 →