Flipkart: Orders Placed Each Day of Big Billion Days

Easy
651.

Flipkart: Orders Placed Each Day of Big Billion Days

During the Big Billion Days sale the war room tracks orders day by day. This year's sale ran from 2024-09-27 00:00:00 to 2024-10-06 23:59:59.

For each calendar day of the sale, return sale_day (as 'YYYY-MM-DD') and orders, the number of orders placed that day whose order_status is not cancelled. Orders placed before or after the sale are ignored, and days with no such order are not listed. Order by sale_day.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: SaleOrder

ColumnTypeValues
order_idint
customer_idint
placed_atdatetime
order_statusenumdelivered, shipped, cancelled, returned

Primary key: order_id.

One row per order; placed_at is the moment it was placed (IST).

Example

The rows must come back in the order shown.

SaleOrder

order_idcustomer_idplaced_atorder_status
11012024-09-26 23:59:59delivered
21022024-09-27 00:00:00delivered
31032024-09-27 14:22:10returned
41012024-09-27 20:05:44cancelled
51042024-09-30 11:40:00shipped
61052024-10-06 23:59:59delivered
71062024-10-07 00:00:00delivered

Output

sale_dayorders
2024-09-272
2024-09-301
2024-10-061
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.

Flipkart: Orders Placed Each Day of Big Billion Days

During the Big Billion Days sale the war room tracks orders day by day. This year's sale ran from 2024-09-27 00:00:00 to 2024-10-06 23:59:59.

  • Difficulty: Easy
  • Topics: Aggregation, Dates
  • Dialect: MySQL
  • Problem: #651

Problem statement

During the Big Billion Days sale the war room tracks orders day by day. This year's sale ran from 2024-09-27 00:00:00 to 2024-10-06 23:59:59.

For each calendar day of the sale, return sale_day (as 'YYYY-MM-DD') and orders, the number of orders placed that day whose order_status is not cancelled. Orders placed before or after the sale are ignored, and days with no such order are not listed. Order by sale_day.

A practice scenario set at Flipkart: the tables and data are invented, and this is not a question from, or endorsed by, Flipkart.

Tables

Table: SaleOrder

ColumnType
order_idint
customer_idint
placed_atdatetime
order_statusenum(delivered, shipped, cancelled, returned)

Primary key: order_id.

One row per order; placed_at is the moment it was placed (IST).

Examples

Example 1

SaleOrder

order_idcustomer_idplaced_atorder_status
11012024-09-26 23:59:59delivered
21022024-09-27 00:00:00delivered
31032024-09-27 14:22:10returned
41012024-09-27 20:05:44cancelled
51042024-09-30 11:40:00shipped
61052024-10-06 23:59:59delivered
71062024-10-07 00:00:00delivered

Output

sale_dayorders
2024-09-272
2024-09-301
2024-10-061

How to solve Flipkart: Orders Placed Each Day of Big Billion Days

Each order belongs to the calendar day in the first ten characters of placed_at, so LEFT(placed_at, 10) is the bucket and COUNT(*) per bucket is the answer. Grouping by the same expression that is selected keeps MySQL's ONLY_FULL_GROUP_BY mode happy.

The sale window is the part to get right. Writing placed_at <= '2024-10-06' would compare against midnight at the start of the last day and lose almost all of it. The half-open range placed_at >= '2024-09-27' AND placed_at < '2024-10-07' covers every second of the ten days without having to spell out 23:59:59, and an order at exactly midnight on 2024-10-07 is correctly out.

Cancelled orders are removed in WHERE, before grouping, so a day whose only orders were cancelled never forms a group. The alternative formats the day with DATE_FORMAT and writes the window as an inclusive BETWEEN. With an index on placed_at the range is read directly.

Reference solution (MySQL)

SELECT LEFT(placed_at, 10) AS sale_day, COUNT(*) AS orders
FROM SaleOrder
WHERE order_status <> 'cancelled'
  AND placed_at >= '2024-09-27' AND placed_at < '2024-10-07'
GROUP BY LEFT(placed_at, 10)
ORDER BY sale_day

Another way

SELECT DATE_FORMAT(placed_at, '%Y-%m-%d') AS sale_day, COUNT(order_id) AS orders
FROM SaleOrder
WHERE order_status IN ('delivered', 'shipped', 'returned')
  AND placed_at BETWEEN '2024-09-27 00:00:00' AND '2024-10-06 23:59:59'
GROUP BY DATE_FORMAT(placed_at, '%Y-%m-%d')
ORDER BY sale_day

← Flipkart: Active F-Assured Listings per Seller · Flipkart: Product Questions Still Waiting for an Answer →