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
| Column | Type |
|---|---|
| restaurant_id | int |
| restaurant_name | varchar |
| locality | varchar |
Primary key: restaurant_id.
One row per listed restaurant.
Table: MenuItem
| Column | Type |
|---|---|
| item_id | int |
| restaurant_id | int |
| item_name | varchar |
| diet | enum(veg, non_veg, egg) |
| is_available | bool |
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_id | restaurant_name | locality |
|---|---|---|
| 1 | Green Leaf Kitchen | Indiranagar |
| 2 | Kebab Korner | Hauz Khas |
| 3 | Udupi Corner | HSR Layout |
| 4 | Rolls Republic | Bandra West |
MenuItem
| item_id | restaurant_id | item_name | diet | is_available |
|---|---|---|---|---|
| 1 | 1 | Paneer Tikka | veg | 1 |
| 2 | 1 | Veg Biryani | veg | 1 |
| 3 | 1 | Masala Omelette | egg | 0 |
| 4 | 2 | Chicken Seekh | non_veg | 1 |
| 5 | 2 | Mutton Galouti | non_veg | 1 |
| 6 | 2 | Egg Roll | egg | 1 |
| 7 | 2 | Dal Makhani | veg | 0 |
| 8 | 3 | Masala Dosa | veg | 1 |
| 9 | 4 | Chicken Roll | non_veg | 0 |
Output
| restaurant_id | restaurant_name | veg_items | non_veg_items | egg_items |
|---|---|---|---|---|
| 1 | Green Leaf Kitchen | 2 | 0 | 0 |
| 2 | Kebab Korner | 0 | 2 | 1 |
| 3 | Udupi Corner | 1 | 0 | 0 |
| 4 | Rolls Republic | 0 | 0 | 0 |
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_nameAnother 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 rAnother 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 →