Myntra: Creator Store Commission for October 2024

Medium
808.

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

ColumnType
creator_idint
handlevarchar
commission_pctint

Primary key: creator_id.

One row per creator; handles are unique. commission_pct is the creator's rate in percent.

Table: CreatorOrder

ColumnTypeValues
order_idint
creator_idint
order_datedate
order_valueint
outcomeenumkept, returned, cancelled

Primary key: order_id.

One row per order placed through a creator's store link.

Example

The rows must come back in the order shown.

Creator

creator_idhandlecommission_pct
1@styledbyriya8
2@kabirfits10
3@zarawears5
4@meera.minimal12

CreatorOrder

order_idcreator_idorder_dateorder_valueoutcome
112024-10-012000kept
212024-10-311500kept
312024-11-013000kept
422024-10-152800kept
522024-10-205000returned
632024-09-304000kept
732024-10-052450cancelled
842024-10-121150kept

Output

handlekept_orderspayout
@kabirfits1280
@styledbyriya2280
@meera.minimal1138
@zarawears00
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: 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

ColumnType
creator_idint
handlevarchar
commission_pctint

Primary key: creator_id.

One row per creator; handles are unique. commission_pct is the creator's rate in percent.

Table: CreatorOrder

ColumnType
order_idint
creator_idint
order_datedate
order_valueint
outcomeenum(kept, returned, cancelled)

Primary key: order_id.

One row per order placed through a creator's store link.

Examples

Example 1

Creator

creator_idhandlecommission_pct
1@styledbyriya8
2@kabirfits10
3@zarawears5
4@meera.minimal12

CreatorOrder

order_idcreator_idorder_dateorder_valueoutcome
112024-10-012000kept
212024-10-311500kept
312024-11-013000kept
422024-10-152800kept
522024-10-205000returned
632024-09-304000kept
732024-10-052450cancelled
842024-10-121150kept

Output

handlekept_orderspayout
@kabirfits1280
@styledbyriya2280
@meera.minimal1138
@zarawears00

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 ASC

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