Zomato: Progressive Commission on Each Restaurant's Monthly Sales
In this exercise a restaurant's delivery commission is charged in slabs, like income tax: each slab's rate applies only to the part of the month's sales…
- Difficulty: Hard
- Topics: Joins, Aggregation, Dates, Window Functions
- Dialect: MySQL
- Problem: #1045
Problem statement
In this exercise a restaurant's delivery commission is charged in slabs, like income tax: each slab's rate applies only to the part of the month's sales that falls inside that slab. The partner-finance team needs each restaurant's monthly commission.
A restaurant's monthly sales is the sum of its order_value over the days of a month. The slabs in CommissionSlab are contiguous: slab 1 starts at 0, each slab starts where the previous one ends, and only the last slab has a NULL upper_bound (no limit). The part of sales s inside a slab is min(s, upper_bound) − lower_bound when s is above lower_bound, and 0 otherwise. For every restaurant and month with sales, return restaurant_id, month (as YYYY-MM), sales, commission = Σ part × rate_pct / 100 rounded to 2 decimals, and top_slab — the highest slab the sales reached. Order by restaurant_id, then month.
A practice scenario set at Zomato: the tables and data are invented, and this is not a question from, or endorsed by, Zomato.
Tables
Table: CommissionSlab
| Column | Type |
|---|---|
| slab_no | int |
| lower_bound | int |
| upper_bound | int |
| rate_pct | int |
Primary key: slab_no.
One row per slab, numbered from 1 in increasing bounds. Amounts in rupees; rate_pct is a percentage.
Table: DailySales
| Column | Type |
|---|---|
| restaurant_id | int |
| sale_date | date |
| order_value | int |
Primary key: restaurant_id, sale_date.
One row per restaurant per day with delivered orders: the day's total order value. Every value is positive.
Examples
Example 1
CommissionSlab
| slab_no | lower_bound | upper_bound | rate_pct |
|---|---|---|---|
| 1 | 0 | 50000 | 25 |
| 2 | 50000 | 200000 | 20 |
| 3 | 200000 | NULL | 15 |
DailySales
| restaurant_id | sale_date | order_value |
|---|---|---|
| 1 | 2024-01-10 | 30000 |
| 1 | 2024-01-31 | 20000 |
| 1 | 2024-02-01 | 120000 |
| 2 | 2024-01-05 | 200000 |
| 2 | 2024-01-06 | 50000 |
| 3 | 2024-01-20 | 1999 |
| 3 | 2024-02-29 | 50001 |
Output
| restaurant_id | month | sales | commission | top_slab |
|---|---|---|---|---|
| 1 | 2024-01 | 50000 | 12500 | 1 |
| 1 | 2024-02 | 120000 | 26500 | 2 |
| 2 | 2024-01 | 250000 | 50000 | 3 |
| 3 | 2024-01 | 1999 | 499.75 | 1 |
| 3 | 2024-02 | 50001 | 12500.2 | 2 |
How to solve Zomato: Progressive Commission on Each Restaurant's Monthly Sales
First aggregate: the slabs apply to a restaurant's monthly sales, so DailySales is summed per restaurant and month, the month written with DATE_FORMAT(sale_date, '%Y-%m') — grouped by the very same expression so MySQL's ONLY_FULL_GROUP_BY accepts it.
Progressive slabs are a range join: each month is joined to every slab whose lower bound its sales exceed. In slab k the charged part is LEAST(sales, upper) − lower — the full width of the slab when the sales go past it, only the excess over lower in the slab where the sales stop. The last slab has no upper bound, and COALESCE(upper_bound, sales) turns "no limit" into "up to the sales". Summing part × rate / 100 over the joined slabs gives the commission, and MAX(slab_no) among them is the top slab reached. The strict > means sales exactly on a boundary stop at the lower slab — 50,000 is charged entirely at slab 1's rate.
The alternative is how tax tables are usually printed: for each slab precompute base, the full commission of all slabs below it (a running SUM over the previous rows), then join each month to the one slab its sales fall in and add only that slab's share. It joins once per month instead of once per reached slab, which matters when the slab table is long. Both are exact on integer amounts; only the final figure is rounded.
Reference solution (MySQL)
WITH monthly AS (
SELECT restaurant_id, DATE_FORMAT(sale_date, '%Y-%m') AS month, SUM(order_value) AS sales
FROM DailySales
GROUP BY restaurant_id, DATE_FORMAT(sale_date, '%Y-%m')
)
SELECT m.restaurant_id, m.month, m.sales,
ROUND(SUM((LEAST(m.sales, COALESCE(c.upper_bound, m.sales)) - c.lower_bound) * c.rate_pct / 100), 2) AS commission,
MAX(c.slab_no) AS top_slab
FROM monthly m
JOIN CommissionSlab c ON m.sales > c.lower_bound
GROUP BY m.restaurant_id, m.month, m.sales
ORDER BY m.restaurant_id, m.monthAnother way
WITH monthly AS (
SELECT restaurant_id, ym AS month, SUM(order_value) AS sales
FROM (SELECT restaurant_id, LEFT(sale_date, 7) AS ym, order_value FROM DailySales) t
GROUP BY restaurant_id, ym
),
slab_base AS (
SELECT slab_no, lower_bound, upper_bound, rate_pct,
COALESCE(SUM((upper_bound - lower_bound) * rate_pct / 100)
OVER (ORDER BY slab_no ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 0) AS base
FROM CommissionSlab
)
SELECT m.restaurant_id, m.month, m.sales,
ROUND(b.base + (m.sales - b.lower_bound) * b.rate_pct / 100, 2) AS commission,
b.slab_no AS top_slab
FROM monthly m
JOIN slab_base b
ON m.sales > b.lower_bound AND (b.upper_bound IS NULL OR m.sales <= b.upper_bound)
ORDER BY m.restaurant_id, m.month← Zomato: Restaurant Partner Anniversaries Falling in November · Zomato: Orders Placed While the Restaurant Was Closed →