Zomato: Progressive Commission on Each Restaurant's Monthly Sales

Hard
1045.

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

ColumnType
slab_noint
lower_boundint
upper_boundint
rate_pctint

Primary key: slab_no.

One row per slab, numbered from 1 in increasing bounds. Amounts in rupees; rate_pct is a percentage.

Table: DailySales

ColumnType
restaurant_idint
sale_datedate
order_valueint

Primary key (together): restaurant_id, sale_date.

One row per restaurant per day with delivered orders: the day's total order value. Every value is positive.

Example

The rows must come back in the order shown.

CommissionSlab

slab_nolower_boundupper_boundrate_pct
105000025
25000020000020
3200000NULL15

DailySales

restaurant_idsale_dateorder_value
12024-01-1030000
12024-01-3120000
12024-02-01120000
22024-01-05200000
22024-01-0650000
32024-01-201999
32024-02-2950001

Output

restaurant_idmonthsalescommissiontop_slab
12024-0150000125001
12024-02120000265002
22024-01250000500003
32024-011999499.751
32024-025000112500.22
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.

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

ColumnType
slab_noint
lower_boundint
upper_boundint
rate_pctint

Primary key: slab_no.

One row per slab, numbered from 1 in increasing bounds. Amounts in rupees; rate_pct is a percentage.

Table: DailySales

ColumnType
restaurant_idint
sale_datedate
order_valueint

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_nolower_boundupper_boundrate_pct
105000025
25000020000020
3200000NULL15

DailySales

restaurant_idsale_dateorder_value
12024-01-1030000
12024-01-3120000
12024-02-01120000
22024-01-05200000
22024-01-0650000
32024-01-201999
32024-02-2950001

Output

restaurant_idmonthsalescommissiontop_slab
12024-0150000125001
12024-02120000265002
22024-01250000500003
32024-011999499.751
32024-025000112500.22

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

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