Zomato: Veg, Non-Veg and Egg Dishes on Every Menu

Easy
1024.

Zomato: Veg, Non-Veg and Egg Dishes on Every Menu

Every dish on Zomato carries a diet marker — the green, red or yellow dot. The menu-quality team wants, for each restaurant, how many dishes it currently offers under each marker.

Return one row for every restaurant with restaurant_id, restaurant_name, veg_items, non_veg_items and egg_items — counts of its menu items with is_available = 1 in each diet. Unavailable items are not counted, and a restaurant with no available items still appears with zeros. Return the rows in any order.

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
localityvarchar

Primary key: restaurant_id.

One row per listed restaurant.

Table: MenuItem

ColumnTypeValues
item_idint
restaurant_idint
item_namevarchar
dietenumveg, non_veg, egg
is_availablebool

Primary key: item_id.

One row per dish. is_available is 0 for a dish switched off for the day.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

Restaurant

restaurant_idrestaurant_namelocality
1Green Leaf KitchenIndiranagar
2Kebab KornerHauz Khas
3Udupi CornerHSR Layout
4Rolls RepublicBandra West

MenuItem

item_idrestaurant_iditem_namedietis_available
11Paneer Tikkaveg1
21Veg Biryaniveg1
31Masala Omeletteegg0
42Chicken Seekhnon_veg1
52Mutton Galoutinon_veg1
62Egg Rollegg1
72Dal Makhaniveg0
83Masala Dosaveg1
94Chicken Rollnon_veg0

Output

restaurant_idrestaurant_nameveg_itemsnon_veg_itemsegg_items
1Green Leaf Kitchen200
2Kebab Korner021
3Udupi Corner100
4Rolls Republic000
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: Veg, Non-Veg and Egg Dishes on Every Menu

Every dish on Zomato carries a diet marker — the green, red or yellow dot.

  • Difficulty: Easy
  • Topics: Joins, Aggregation, Conditional Logic
  • Dialect: MySQL
  • Problem: #1024

Problem statement

Every dish on Zomato carries a diet marker — the green, red or yellow dot. The menu-quality team wants, for each restaurant, how many dishes it currently offers under each marker.

Return one row for every restaurant with restaurant_id, restaurant_name, veg_items, non_veg_items and egg_items — counts of its menu items with is_available = 1 in each diet. Unavailable items are not counted, and a restaurant with no available items still appears with zeros. Return the rows in any order.

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
localityvarchar

Primary key: restaurant_id.

One row per listed restaurant.

Table: MenuItem

ColumnType
item_idint
restaurant_idint
item_namevarchar
dietenum(veg, non_veg, egg)
is_availablebool

Primary key: item_id.

One row per dish. is_available is 0 for a dish switched off for the day.

Examples

Example 1

Restaurant

restaurant_idrestaurant_namelocality
1Green Leaf KitchenIndiranagar
2Kebab KornerHauz Khas
3Udupi CornerHSR Layout
4Rolls RepublicBandra West

MenuItem

item_idrestaurant_iditem_namedietis_available
11Paneer Tikkaveg1
21Veg Biryaniveg1
31Masala Omeletteegg0
42Chicken Seekhnon_veg1
52Mutton Galoutinon_veg1
62Egg Rollegg1
72Dal Makhaniveg0
83Masala Dosaveg1
94Chicken Rollnon_veg0

Output

restaurant_idrestaurant_nameveg_itemsnon_veg_itemsegg_items
1Green Leaf Kitchen200
2Kebab Korner021
3Udupi Corner100
4Rolls Republic000

How to solve Zomato: Veg, Non-Veg and Egg Dishes on Every Menu

Start from Restaurant, because every restaurant must be in the answer, and LEFT JOIN its menu items. A restaurant with no matching item still produces one row whose MenuItem columns are all NULL.

The availability test belongs in the join condition, not in WHERE. Put in WHERE, m.is_available = 1 is NULL for the unmatched row and removes it, so a restaurant whose dishes are all switched off would vanish instead of showing zeros.

Each count is then a conditional aggregate: COUNT(CASE WHEN m.diet = 'veg' THEN 1 END) counts only the rows where the CASE yields a value, and a NULL-padded row yields nothing, so it contributes 0. One pass over the joined rows gives all three columns.

The alternatives use three correlated COUNT(*) subqueries (simple, but three probes per restaurant), and a pre-filtered derived table summed with IF — where the COALESCE matters, because SUM over only NULLs is NULL rather than 0.

Reference solution (MySQL)

SELECT r.restaurant_id, r.restaurant_name,
       COUNT(CASE WHEN m.diet = 'veg' THEN 1 END) AS veg_items,
       COUNT(CASE WHEN m.diet = 'non_veg' THEN 1 END) AS non_veg_items,
       COUNT(CASE WHEN m.diet = 'egg' THEN 1 END) AS egg_items
FROM Restaurant r
LEFT JOIN MenuItem m ON m.restaurant_id = r.restaurant_id AND m.is_available = 1
GROUP BY r.restaurant_id, r.restaurant_name

Another way

SELECT r.restaurant_id, r.restaurant_name,
  (SELECT COUNT(*) FROM MenuItem m WHERE m.restaurant_id = r.restaurant_id AND m.is_available = 1 AND m.diet = 'veg') AS veg_items,
  (SELECT COUNT(*) FROM MenuItem m WHERE m.restaurant_id = r.restaurant_id AND m.is_available = 1 AND m.diet = 'non_veg') AS non_veg_items,
  (SELECT COUNT(*) FROM MenuItem m WHERE m.restaurant_id = r.restaurant_id AND m.is_available = 1 AND m.diet = 'egg') AS egg_items
FROM Restaurant r

Another way

SELECT r.restaurant_id, r.restaurant_name,
       COALESCE(SUM(IF(a.diet = 'veg', 1, 0)), 0) AS veg_items,
       COALESCE(SUM(IF(a.diet = 'non_veg', 1, 0)), 0) AS non_veg_items,
       COALESCE(SUM(IF(a.diet = 'egg', 1, 0)), 0) AS egg_items
FROM Restaurant r
LEFT JOIN (SELECT restaurant_id, diet FROM MenuItem WHERE is_available = 1) a ON a.restaurant_id = r.restaurant_id
GROUP BY r.restaurant_id, r.restaurant_name

← Zomato: Hygiene Certificates Expiring in July · Zomato: Gold Members Yet to Use a Single Benefit →