Zomato: Cost per Order of Each Promoted Listing

Medium
1032.

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.

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

ColumnType
restaurant_idint
restaurant_namevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: AdCampaign

ColumnType
campaign_idint
restaurant_idint
start_datedate
end_datedate
spendint

Primary key: campaign_id.

One row per promoted-listing campaign; spend is the rupees billed for it.

Table: FoodOrder

ColumnType
order_idint
restaurant_idint
ordered_ondate
from_adbool

Primary key: order_id.

One row per order; from_ad is 1 when the customer reached the restaurant by tapping its promoted listing.

Example

The rows must come back in the order shown.

Restaurant

restaurant_idrestaurant_name
1Pasta Street
2Momo Point
3Thali Theatre

AdCampaign

campaign_idrestaurant_idstart_dateend_datespend
112024-05-012024-05-073000
222024-05-032024-05-051500
312024-05-202024-05-21800
432024-05-102024-05-121200

FoodOrder

order_idrestaurant_idordered_onfrom_ad
112024-05-011
212024-05-071
312024-05-040
412024-05-081
522024-05-031
622024-05-041
722024-05-051
832024-05-110
912024-05-211

Output

campaign_idrestaurant_namead_orderscost_per_order
1Pasta Street21500
2Momo Point3500
3Pasta Street1800
4Thali Theatre0NULL
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: 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

ColumnType
restaurant_idint
restaurant_namevarchar

Primary key: restaurant_id.

One row per restaurant.

Table: AdCampaign

ColumnType
campaign_idint
restaurant_idint
start_datedate
end_datedate
spendint

Primary key: campaign_id.

One row per promoted-listing campaign; spend is the rupees billed for it.

Table: FoodOrder

ColumnType
order_idint
restaurant_idint
ordered_ondate
from_adbool

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_idrestaurant_name
1Pasta Street
2Momo Point
3Thali Theatre

AdCampaign

campaign_idrestaurant_idstart_dateend_datespend
112024-05-012024-05-073000
222024-05-032024-05-051500
312024-05-202024-05-21800
432024-05-102024-05-121200

FoodOrder

order_idrestaurant_idordered_onfrom_ad
112024-05-011
212024-05-071
312024-05-040
412024-05-081
522024-05-031
622024-05-041
722024-05-051
832024-05-110
912024-05-211

Output

campaign_idrestaurant_namead_orderscost_per_order
1Pasta Street21500
2Momo Point3500
3Pasta Street1800
4Thali Theatre0NULL

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_id

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