Myntra: Delivered GMV by Indian Fiscal Year and Quarter
Finance reports on the Indian fiscal year, which runs from 1 April to 31 March, and wants the board pack's sales table in those terms rather than by…
- Difficulty: Medium
- Topics: Dates, Aggregation, Strings
- Dialect: MySQL
- Problem: #811
Problem statement
Finance reports on the Indian fiscal year, which runs from 1 April to 31 March, and wants the board pack's sales table in those terms rather than by calendar year.
Count only orders with status = 'delivered'. Label each order with its fiscal year as 'FY<start year>-<last two digits of the end year>' — an order on 15 January 2025 belongs to 'FY2024-25', one on 1 April 2025 to 'FY2025-26' — and its fiscal quarter 'Q1' (April–June), 'Q2' (July–September), 'Q3' (October–December) or 'Q4' (January–March). Return fiscal_year, fiscal_quarter, orders (delivered orders) and gmv (the sum of their amount) for every fiscal quarter with at least one delivered order, ordered by fiscal_year, then fiscal_quarter.
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: ShopOrder
| Column | Type |
|---|---|
| order_id | int |
| user_id | int |
| placed_on | date |
| amount | int |
| status | enum(delivered, returned, cancelled) |
Primary key: order_id.
One row per order; amount is what the shopper paid, in rupees. Returned and cancelled orders are not GMV.
Examples
Example 1
ShopOrder
| order_id | user_id | placed_on | amount | status |
|---|---|---|---|---|
| 1 | 11 | 2024-03-31 | 1800 | delivered |
| 2 | 12 | 2024-04-01 | 2500 | delivered |
| 3 | 13 | 2024-06-30 | 900 | delivered |
| 4 | 11 | 2024-07-01 | 1200 | returned |
| 5 | 14 | 2024-12-31 | 3100 | delivered |
| 6 | 12 | 2025-01-01 | 700 | delivered |
| 7 | 15 | 2025-03-15 | 1500 | cancelled |
| 8 | 13 | 2025-03-31 | 1100 | delivered |
| 9 | 14 | 2025-04-01 | 2000 | delivered |
Output
| fiscal_year | fiscal_quarter | orders | gmv |
|---|---|---|---|
| FY2023-24 | Q4 | 1 | 1800 |
| FY2024-25 | Q1 | 2 | 3400 |
| FY2024-25 | Q3 | 1 | 3100 |
| FY2024-25 | Q4 | 2 | 1800 |
| FY2025-26 | Q1 | 1 | 2000 |
How to solve Myntra: Delivered GMV by Indian Fiscal Year and Quarter
Every date maps to exactly one fiscal year and quarter, so the task is a relabelling followed by a plain GROUP BY. The fiscal year starts in April: a date in April–December belongs to the fiscal year starting that calendar year, and a date in January–March to the one that started the year before. That is the CASE on MONTH(placed_on) >= 4. The quarter is a second CASE over month ranges — April–June Q1 through January–March Q4. The label then joins the start year with the last two digits of the next year, padded so FY2099-00 would still read correctly.
The boundaries are where mistakes happen, and the example sits on them: 31 March 2024 is Q4 of FY2023-24, the next day is Q1 of FY2024-25; 31 December and 1 January are Q3 and Q4 of the same fiscal year; 31 March 2025 closes FY2024-25. Returned and cancelled orders are filtered before grouping, so the returned July order leaves FY2024-25 with no Q2 row at all.
The alternative uses a neat shift: DATE_SUB(placed_on, INTERVAL 3 MONTH) moves April to January and March to December, after which YEAR() is the fiscal start year and QUARTER() is the fiscal quarter. Both are one pass over the orders and one grouping; since the labels are fixed-width text, sorting them as strings is chronological.
Reference solution (MySQL)
SELECT CONCAT('FY', fy, '-', LPAD((fy + 1) MOD 100, 2, '0')) AS fiscal_year,
CONCAT('Q', fq) AS fiscal_quarter,
COUNT(*) AS orders,
SUM(amount) AS gmv
FROM (
SELECT amount,
CASE WHEN MONTH(placed_on) >= 4 THEN YEAR(placed_on) ELSE YEAR(placed_on) - 1 END AS fy,
CASE WHEN MONTH(placed_on) BETWEEN 4 AND 6 THEN 1
WHEN MONTH(placed_on) BETWEEN 7 AND 9 THEN 2
WHEN MONTH(placed_on) BETWEEN 10 AND 12 THEN 3
ELSE 4 END AS fq
FROM ShopOrder
WHERE status = 'delivered'
) t
GROUP BY fy, fq
ORDER BY fiscal_year, fiscal_quarterAnother way
WITH shifted AS (
SELECT amount, DATE_SUB(placed_on, INTERVAL 3 MONTH) AS d
FROM ShopOrder
WHERE status = 'delivered'
), labelled AS (
SELECT amount,
CONCAT('FY', YEAR(d), '-', RIGHT(CONCAT(YEAR(d) + 1), 2)) AS fiscal_year,
CONCAT('Q', QUARTER(d)) AS fiscal_quarter
FROM shifted
)
SELECT fiscal_year, fiscal_quarter, COUNT(*) AS orders, SUM(amount) AS gmv
FROM labelled
GROUP BY fiscal_year, fiscal_quarter
ORDER BY fiscal_year, fiscal_quarter← Myntra: Wishlist-to-Purchase Conversion by Article Type · Myntra: Jeans Buyers Whose Waist Size Changed Between Orders →