Myntra: Creator Store Commission for October 2024
Fashion creators run storefronts on the app and earn a commission on the orders that come through their links.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #808
Problem statement
Fashion creators run storefronts on the app and earn a commission on the orders that come through their links. Finance is preparing October's payout run, and every creator gets a line, even one who earned nothing.
A creator earns order_value * commission_pct / 100 on each order with outcome = 'kept' and an order_date in October 2024. Returned and cancelled orders earn nothing. For every creator return handle, kept_orders (their qualifying orders) and payout (the total, 0 when there are none), ordered by payout descending, then handle ascending.
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: Creator
| Column | Type |
|---|---|
| creator_id | int |
| handle | varchar |
| commission_pct | int |
Primary key: creator_id.
One row per creator; handles are unique. commission_pct is the creator's rate in percent.
Table: CreatorOrder
| Column | Type |
|---|---|
| order_id | int |
| creator_id | int |
| order_date | date |
| order_value | int |
| outcome | enum(kept, returned, cancelled) |
Primary key: order_id.
One row per order placed through a creator's store link.
Examples
Example 1
Creator
| creator_id | handle | commission_pct |
|---|---|---|
| 1 | @styledbyriya | 8 |
| 2 | @kabirfits | 10 |
| 3 | @zarawears | 5 |
| 4 | @meera.minimal | 12 |
CreatorOrder
| order_id | creator_id | order_date | order_value | outcome |
|---|---|---|---|---|
| 1 | 1 | 2024-10-01 | 2000 | kept |
| 2 | 1 | 2024-10-31 | 1500 | kept |
| 3 | 1 | 2024-11-01 | 3000 | kept |
| 4 | 2 | 2024-10-15 | 2800 | kept |
| 5 | 2 | 2024-10-20 | 5000 | returned |
| 6 | 3 | 2024-09-30 | 4000 | kept |
| 7 | 3 | 2024-10-05 | 2450 | cancelled |
| 8 | 4 | 2024-10-12 | 1150 | kept |
Output
| handle | kept_orders | payout |
|---|---|---|
| @kabirfits | 1 | 280 |
| @styledbyriya | 2 | 280 |
| @meera.minimal | 1 | 138 |
| @zarawears | 0 | 0 |
How to solve Myntra: Creator Store Commission for October 2024
A payout run needs a line for every creator, so the query starts from Creator and LEFT JOINs the orders. The three conditions that decide which orders count — the creator, outcome = 'kept', and an October 2024 date — all go into the ON clause. If the outcome or date test sat in WHERE instead, a creator whose only orders were returned or fell in September would lose their NULL-extended row and disappear from the run.
After grouping per creator, COUNT(o.order_id) counts matched orders only (the NULL row of a creator with none counts 0), and SUM(o.order_value * c.commission_pct / 100) adds each order's commission; for a creator with no matches that sum is NULL, which COALESCE turns into 0. The date range is inclusive at both ends, so 1 and 31 October count while 30 September and 1 November do not.
The result is sorted by payout, highest first, and two creators with the same payout by handle. The alternative computes the count and the kept value with two correlated subqueries and applies the rate once to the total — the same amount, since the rate is constant per creator.
Reference solution (MySQL)
SELECT c.handle,
COUNT(o.order_id) AS kept_orders,
COALESCE(SUM(o.order_value * c.commission_pct / 100), 0) AS payout
FROM Creator c
LEFT JOIN CreatorOrder o ON o.creator_id = c.creator_id
AND o.outcome = 'kept'
AND o.order_date BETWEEN '2024-10-01' AND '2024-10-31'
GROUP BY c.creator_id, c.handle, c.commission_pct
ORDER BY payout DESC, c.handle ASCAnother way
SELECT handle, kept_orders, kept_value * commission_pct / 100 AS payout
FROM (
SELECT c.handle, c.commission_pct,
(SELECT COUNT(*) FROM CreatorOrder o WHERE o.creator_id = c.creator_id AND o.outcome = 'kept'
AND YEAR(o.order_date) = 2024 AND MONTH(o.order_date) = 10) AS kept_orders,
(SELECT COALESCE(SUM(o.order_value), 0) FROM CreatorOrder o WHERE o.creator_id = c.creator_id AND o.outcome = 'kept'
AND YEAR(o.order_date) = 2024 AND MONTH(o.order_date) = 10) AS kept_value
FROM Creator c
) t
ORDER BY payout DESC, handle← Myntra: Shoppers Who Reordered the Size They Returned as Too Small · Myntra: Styles Marked Down by at Least 40% for EORS Day One →