Zomato: Cost per Order of Each Promoted Listing
Restaurants pay Zomato to appear as promoted listings, and the ads sales team reports what each campaign cost per order it brought in.
- Difficulty: Medium
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #1032
Problem statement
Restaurants pay Zomato to appear as promoted listings, and the ads sales team reports what each campaign cost per order it brought in.
An order is attributed to a campaign when it was placed at the campaign's restaurant, has from_ad = 1, and its ordered_on falls within the campaign's dates (inclusive); an order inside two overlapping campaigns of one restaurant counts for both. For every campaign return campaign_id, restaurant_name, ad_orders (attributed orders) and cost_per_order = spend / ad_orders rounded to 2 decimals — NULL when the campaign has no attributed order. Order by campaign_id.
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: Restaurant
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
Primary key: restaurant_id.
One row per restaurant.
Table: AdCampaign
| Column | Type |
|---|---|
| campaign_id | int |
| restaurant_id | int |
| start_date | date |
| end_date | date |
| spend | int |
Primary key: campaign_id.
One row per promoted-listing campaign; spend is the rupees billed for it.
Table: FoodOrder
| Column | Type |
|---|---|
| order_id | int |
| restaurant_id | int |
| ordered_on | date |
| from_ad | bool |
Primary key: order_id.
One row per order; from_ad is 1 when the customer reached the restaurant by tapping its promoted listing.
Examples
Example 1
Restaurant
| restaurant_id | restaurant_name |
|---|---|
| 1 | Pasta Street |
| 2 | Momo Point |
| 3 | Thali Theatre |
AdCampaign
| campaign_id | restaurant_id | start_date | end_date | spend |
|---|---|---|---|---|
| 1 | 1 | 2024-05-01 | 2024-05-07 | 3000 |
| 2 | 2 | 2024-05-03 | 2024-05-05 | 1500 |
| 3 | 1 | 2024-05-20 | 2024-05-21 | 800 |
| 4 | 3 | 2024-05-10 | 2024-05-12 | 1200 |
FoodOrder
| order_id | restaurant_id | ordered_on | from_ad |
|---|---|---|---|
| 1 | 1 | 2024-05-01 | 1 |
| 2 | 1 | 2024-05-07 | 1 |
| 3 | 1 | 2024-05-04 | 0 |
| 4 | 1 | 2024-05-08 | 1 |
| 5 | 2 | 2024-05-03 | 1 |
| 6 | 2 | 2024-05-04 | 1 |
| 7 | 2 | 2024-05-05 | 1 |
| 8 | 3 | 2024-05-11 | 0 |
| 9 | 1 | 2024-05-21 | 1 |
Output
| campaign_id | restaurant_name | ad_orders | cost_per_order |
|---|---|---|---|
| 1 | Pasta Street | 2 | 1500 |
| 2 | Momo Point | 3 | 500 |
| 3 | Pasta Street | 1 | 800 |
| 4 | Thali Theatre | 0 | NULL |
How to solve Zomato: Cost per Order of Each Promoted Listing
Each campaign is matched with the orders it earned: same restaurant, from_ad = 1, and ordered_on BETWEEN start_date AND end_date. Those three conditions all belong in the ON clause of a LEFT JOIN, so that a campaign that earned nothing still contributes one row with NULL order columns. Moved into WHERE, they would delete that row and the campaign with it.
After grouping by campaign, COUNT(o.order_id) counts only real matches — it skips the NULL of the padded row, so an empty campaign shows 0, where COUNT(*) would wrongly show 1. The cost per order is spend / ad_orders; dividing by NULLIF(COUNT(o.order_id), 0) turns the zero case into NULL, as the statement asks. spend is in the GROUP BY so it can be used beside the aggregate.
Overlapping campaigns of one restaurant each join to the shared orders independently, so such an order counts for both — that is the stated rule. The alternative counts with a correlated subquery per campaign and handles zero with CASE. Both are a range probe per campaign with an index on FoodOrder(restaurant_id, ordered_on).
Reference solution (MySQL)
SELECT c.campaign_id, r.restaurant_name,
COUNT(o.order_id) AS ad_orders,
ROUND(c.spend / NULLIF(COUNT(o.order_id), 0), 2) AS cost_per_order
FROM AdCampaign c
JOIN Restaurant r ON r.restaurant_id = c.restaurant_id
LEFT JOIN FoodOrder o
ON o.restaurant_id = c.restaurant_id
AND o.from_ad = 1
AND o.ordered_on BETWEEN c.start_date AND c.end_date
GROUP BY c.campaign_id, r.restaurant_name, c.spend
ORDER BY c.campaign_idAnother way
SELECT campaign_id, restaurant_name, ad_orders,
CASE WHEN ad_orders = 0 THEN NULL ELSE ROUND(spend / ad_orders, 2) END AS cost_per_order
FROM (
SELECT c.campaign_id, r.restaurant_name, c.spend,
(SELECT COUNT(*) FROM FoodOrder o
WHERE o.restaurant_id = c.restaurant_id AND o.from_ad = 1
AND o.ordered_on >= c.start_date AND o.ordered_on <= c.end_date) AS ad_orders
FROM AdCampaign c JOIN Restaurant r ON r.restaurant_id = c.restaurant_id
) t
ORDER BY campaign_id← Zomato: Gold Membership Renewal Rate by Plan · Zomato: Best-Rated Dining Spot in Each Locality →