Myntra: Monthly Net Revenue After Refunds in 2024
Finance books an order's refunds against the month the order was placed, not the month the money went back, so a month's net figure stays tied to the sales…
- Difficulty: Medium
- Topics: Aggregation, Dates, Joins
- Dialect: MySQL
- Problem: #814
Problem statement
Finance books an order's refunds against the month the order was placed, not the month the money went back, so a month's net figure stays tied to the sales it came from. An order can be refunded in several parts.
For each month of 2024 with at least one order whose status is delivered, return order_month (as 'YYYY-MM'), gross (the total order_value of delivered orders placed that month), refunds (the total of all refunds on those orders, whenever they were made; 0 if none) and net = gross - refunds. Order the rows by order_month.
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 |
| placed_on | date |
| order_value | int |
| status | enum(delivered, cancelled) |
Primary key: order_id.
One row per order.
Table: Refund
| Column | Type |
|---|---|
| refund_id | int |
| order_id | int |
| refunded_on | date |
| amount | int |
Primary key: refund_id.
One row per refund payment; an order may have several (one per returned item).
Examples
Example 1
ShopOrder
| order_id | placed_on | order_value | status |
|---|---|---|---|
| 1 | 2024-01-10 | 3000 | delivered |
| 2 | 2024-01-25 | 2000 | delivered |
| 3 | 2024-01-28 | 1500 | cancelled |
| 4 | 2024-02-03 | 4000 | delivered |
| 5 | 2023-12-31 | 2500 | delivered |
| 6 | 2024-03-15 | 1000 | delivered |
Refund
| refund_id | order_id | refunded_on | amount |
|---|---|---|---|
| 1 | 1 | 2024-01-20 | 1000 |
| 2 | 1 | 2024-02-02 | 500 |
| 3 | 4 | 2024-02-10 | 4000 |
| 4 | 5 | 2024-01-05 | 2500 |
Output
| order_month | gross | refunds | net |
|---|---|---|---|
| 2024-01 | 5000 | 1500 | 3500 |
| 2024-02 | 4000 | 4000 | 0 |
| 2024-03 | 1000 | 0 | 1000 |
How to solve Myntra: Monthly Net Revenue After Refunds in 2024
The trap is join fan-out. Order 1 was refunded in two parts; joined straight to Refund it becomes two rows, and SUM(order_value) would count its ₹3000 twice. The fix is to bring the refunds down to one row per order before the join: the per_order CTE sums amount by order_id, and that is LEFT JOINed so orders without refunds stay in with a NULL.
Then the delivered 2024 orders are grouped by DATE_FORMAT(placed_on, '%Y-%m'). Gross is the sum of order values; refunds is the sum of the per-order refund totals, wrapped in COALESCE so a month with no refunds shows 0 rather than NULL (which would also make net NULL). The month is always the order's: order 1's February refund lands in January, and order 5's refund in January 2024 belongs to a December 2023 order and is outside the report.
The alternative puts each order's refund total in a correlated subquery inside a derived table — also one value per order, so no fan-out — and takes the month with LEFT(placed_on, 7). Both read each table once with an index on Refund(order_id).
Reference solution (MySQL)
WITH per_order AS (
SELECT order_id, SUM(amount) AS refunded
FROM Refund
GROUP BY order_id
)
SELECT DATE_FORMAT(o.placed_on, '%Y-%m') AS order_month,
SUM(o.order_value) AS gross,
COALESCE(SUM(p.refunded), 0) AS refunds,
SUM(o.order_value) - COALESCE(SUM(p.refunded), 0) AS net
FROM ShopOrder o
LEFT JOIN per_order p ON p.order_id = o.order_id
WHERE o.status = 'delivered' AND o.placed_on >= '2024-01-01' AND o.placed_on < '2025-01-01'
GROUP BY order_month
ORDER BY order_monthAnother way
SELECT order_month, SUM(order_value) AS gross, SUM(refunded) AS refunds, SUM(order_value) - SUM(refunded) AS net
FROM (
SELECT LEFT(o.placed_on, 7) AS order_month, o.order_value,
(SELECT COALESCE(SUM(r.amount), 0) FROM Refund r WHERE r.order_id = o.order_id) AS refunded
FROM ShopOrder o
WHERE o.status = 'delivered' AND YEAR(o.placed_on) = 2024
) t
GROUP BY order_month
ORDER BY order_month← Myntra: Size Exchanges Blocked by an Unavailable Size, per Brand · Myntra: Styles Most Shoppers Say Run Small →