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
| Column | Type |
|---|---|
| order_id | int |
| customer_id | int |
| placed_at | datetime |
| order_status | enum(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_id | customer_id | placed_at | order_status |
|---|---|---|---|
| 1 | 101 | 2024-09-26 23:59:59 | delivered |
| 2 | 102 | 2024-09-27 00:00:00 | delivered |
| 3 | 103 | 2024-09-27 14:22:10 | returned |
| 4 | 101 | 2024-09-27 20:05:44 | cancelled |
| 5 | 104 | 2024-09-30 11:40:00 | shipped |
| 6 | 105 | 2024-10-06 23:59:59 | delivered |
| 7 | 106 | 2024-10-07 00:00:00 | delivered |
Output
| sale_day | orders |
|---|---|
| 2024-09-27 | 2 |
| 2024-09-30 | 1 |
| 2024-10-06 | 1 |
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_dayAnother 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 →