Zomato: Top 5 Most-Ordered Dishes at a Restaurant

SQL coding challenge · Difficulty: medium · Topic: Window Functions · +100 XP

Problem

*Asked in a Zomato data interview.*

Zomato shows every restaurant partner its top 5 most-ordered dishes. Write a query that builds this list for the restaurant R101 from the orders table.

Only delivered orders count. Cancelled orders are ignored completely.
A dish's popularity is its total quantity: the sum of quantity over its delivered orders at R101. It is not the number of orders.
Rank the dishes by total quantity, highest first. Dishes with the same total share a rank, and the ranks after a tie are skipped (9, 7, 7, 5 gives ranks 1, 2, 2, 4).
Return every dish whose rank is 5 or better. When dishes tie at 5th place, all of them are returned, so the answer can have more than 5 rows.
If the restaurant has fewer than 5 dishes, return all of them.
A dish with no delivered order at R101 does not appear at all.

Return dish_name, total_quantity and dish_rank. Sort by dish_rank, then dish_name (the grader checks the rows, not their order).

Schema

Table: orders

| Column | Type | Meaning |
| --- | --- | --- |
| order_id | INT | One order line |
| dish_name | VARCHAR | The dish ordered |
| restaurant_id | VARCHAR | The restaurant that received the order |
| quantity | INT | Units of the dish in this order (at least one) |
| order_date | DATE | Day of the order (not needed here) |
| status | VARCHAR | `delivered` or `cancelled` (always lowercase) |

Tables

Table: orders

| order_id | dish_name | restaurant_id | quantity | order_date | status |
| --- | --- | --- | --- | --- | --- |
| 1 | Biryani | R101 | 5 | 2025-01-01 | delivered |
| 2 | Burger | R101 | 2 | 2025-01-01 | delivered |
| 3 | Paneer Tikka | R101 | 4 | 2025-01-02 | delivered |
| 4 | Biryani | R101 | 4 | 2025-01-02 | delivered |
| 5 | Pasta | R101 | 1 | 2025-01-02 | delivered |
| 6 | Pizza | R102 | 20 | 2025-01-03 | delivered |
| 7 | Burger | R101 | 5 | 2025-01-03 | delivered |
| 8 | Momos | R101 | 10 | 2025-01-03 | cancelled |
| 9 | Paneer Tikka | R101 | 3 | 2025-01-04 | delivered |
| 10 | Pasta | R101 | 4 | 2025-01-04 | delivered |
| 11 | Dosa | R101 | 4 | 2025-01-04 | delivered |
| 12 | Noodles | R101 | 1 | 2025-01-05 | delivered |
| 13 | Pizza | R101 | 3 | 2025-01-05 | delivered |
| 14 | Noodles | R101 | 1 | 2025-01-05 | delivered |
| 15 | Noodles | R101 | 1 | 2025-01-06 | delivered |
| 16 | Pizza | R101 | 1 | 2025-01-06 | delivered |
| 17 | Momos | R101 | 1 | 2025-01-06 | delivered |
| 18 | Burger | R101 | 6 | 2025-01-06 | cancelled |

Expected Output

| dish_name | total_quantity | dish_rank |
| --- | --- | --- |
| Biryani | 9 | 1 |
| Burger | 7 | 2 |
| Paneer Tikka | 7 | 2 |
| Pasta | 5 | 4 |
| Dosa | 4 | 5 |
| Pizza | 4 | 5 |

Explanation

Biryani 5 + 4 = 9, rank 1.
Burger 2 + 5 = 7 (order 18 was cancelled) and Paneer Tikka 4 + 3 = 7 tie at rank 2, so the next rank is 4.
Pasta 1 + 4 = 5, rank 4.
Dosa 4 and Pizza 3 + 1 = 4 tie at rank 5, so both are returned. Order 6 (Pizza 20) is at R102 and does not count.
Noodles has the most orders (3) but only 3 units: rank 7, left out.
Momos has 1 delivered unit; its 10-unit order was cancelled: rank 8, left out.

Notes

The restaurant is always R101. Other restaurants' orders are in the same table.
Column names must be exactly dish_name, total_quantity and dish_rank.
RANK is a reserved word in MySQL 8, so the column is called dish_rank.

What this SQL challenge teaches you

“Zomato: Top 5 Most-Ordered Dishes at a Restaurant” is a medium-level SQL challenge focused on Window Functions. Working through it gives you hands-on practice with GROUP BY, SUM, RANK, Window Functions, Ties, Top-N — the kind of transformation you are asked to write in real data engineering work and in technical interviews. You can solve it directly in the browser: the dataset is pre-loaded, so you write the query or DataFrame code, run it, and compare your output against the expected result immediately.

Concepts covered

GROUP BY
SUM
RANK
Window Functions
Ties
Top-N
Food Delivery

How to approach it

If you get stuck, work through these steps in order before looking at a full solution — each one narrows the problem down:

Filter first: WHERE restaurant_id = 'R101' AND status = 'delivered'. Then GROUP BY dish_name and SUM(quantity).
ORDER BY ... LIMIT 5 always returns exactly 5 rows, so it cannot keep two dishes that tie at 5th place. Number the dishes with a window function instead, and filter on that number.
ROW_NUMBER gives tied dishes different numbers, DENSE_RANK does not skip after a tie. RANK() OVER (ORDER BY total_quantity DESC) is the one that gives 1, 2, 2, 4.
You cannot filter on a window function in the same SELECT's WHERE. Compute it in a CTE or subquery, then keep dish_rank <= 5 outside.

Where this comes up

Variations of this problem have been reported in interviews at Zomato. Interviewers use it to check whether you can express the logic cleanly and reason about correctness on edge cases such as ties, nulls and empty groups.

How to practise it on PySpark.in

Open the challenge, write your SQL query in the editor and press Run to execute it against the sample dataset. Submitting checks your output against every test case, including hidden ones, so you find out straight away whether your logic holds up. You can retry as often as you like, and each solved challenge adds to your XP.

Related SQL challenges

Helpful resources

Frequently asked questions

Do I need to install Spark or a database to solve this?

No. The SQL environment runs in your browser with the sample data already loaded, so there is nothing to install or configure.

Is this challenge free?

Yes - the problem, the sample dataset, the hints and unlimited test runs are free.

What level is it?

It is rated medium and covers Window Functions.

Solve this challenge free on PySpark.in