Myntra: Monthly Net Revenue After Refunds in 2024

Medium
814.

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 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

ColumnTypeValues
order_idint
placed_ondate
order_valueint
statusenumdelivered, cancelled

Primary key: order_id.

One row per order.

Table: Refund

ColumnType
refund_idint
order_idint
refunded_ondate
amountint

Primary key: refund_id.

One row per refund payment; an order may have several (one per returned item).

Example

The rows must come back in the order shown.

ShopOrder

order_idplaced_onorder_valuestatus
12024-01-103000delivered
22024-01-252000delivered
32024-01-281500cancelled
42024-02-034000delivered
52023-12-312500delivered
62024-03-151000delivered

Refund

refund_idorder_idrefunded_onamount
112024-01-201000
212024-02-02500
342024-02-104000
452024-01-052500

Output

order_monthgrossrefundsnet
2024-01500015003500
2024-02400040000
2024-03100001000
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: 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

ColumnType
order_idint
placed_ondate
order_valueint
statusenum(delivered, cancelled)

Primary key: order_id.

One row per order.

Table: Refund

ColumnType
refund_idint
order_idint
refunded_ondate
amountint

Primary key: refund_id.

One row per refund payment; an order may have several (one per returned item).

Examples

Example 1

ShopOrder

order_idplaced_onorder_valuestatus
12024-01-103000delivered
22024-01-252000delivered
32024-01-281500cancelled
42024-02-034000delivered
52023-12-312500delivered
62024-03-151000delivered

Refund

refund_idorder_idrefunded_onamount
112024-01-201000
212024-02-02500
342024-02-104000
452024-01-052500

Output

order_monthgrossrefundsnet
2024-01500015003500
2024-02400040000
2024-03100001000

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_month

Another 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 →