11 Zomato SQL Interview Questions (Updated 2024)
At Zomato, SQL crucial for analyzing restaurant performance metrics and generating insights about the delivery-driver workforce. Because of this, Zomato LOVES to ask SQL query questions during interviews for Data Science and Data Engineering positions.
So, to help you practice for the Zomato SQL interview, we've collected 11 Zomato SQL interview questions – able to answer them all?
11 Zomato SQL Interview Questions
SQL Question 1: Swapped Food Delivery
Recently, Zomato encountered an issue with their delivery system. Due to an error in the delivery driver instructions, each item's order was swapped with the item in the subsequent row. As a data analyst, you're asked to correct this swapping error and return the proper pairing of order ID and item.
If the last item has an odd order ID, it should remain as the last item in the corrected data. For example, if the last item is Order ID 7 Tandoori Chicken, then it should remain as Order ID 7 in the corrected data.
In the results, return the correct pairs of order IDs and items.
Schema:
| column_name | type | description |
|---|---|---|
| order_id | integer | The ID of each Zomato order. |
| item | string | The name of the food item in each order. |
Example Input:
Here's a sample of the initial incorrect data:
| order_id | item |
|---|---|
| 1 | Chow Mein |
| 2 | Pizza |
| 3 | Pad Thai |
| 4 | Butter Chicken |
| 5 | Eggrolls |
| 6 | Burger |
| 7 | Tandoori Chicken |
Example Output:
The corrected data should look like this:
| corrected_order_id | item |
|---|---|
| 1 | Pizza |
| 2 | Chow Mein |
| 3 | Butter Chicken |
| 4 | Pad Thai |
| 5 | Burger |
| 6 | Eggrolls |
| 7 | Tandoori Chicken |
Answer:
To practice this question and test out alternative solutions try our Interactive Coding Site for FREE!
SQL Question 2: Analyze Restaurant Ratings Over Time
As a data analyst for Zomato, you have been tasked with tracking the average ratings for each restaurant over time. The data you have been provided with includes individual reviews, with the reviewer's ID, the date of the review, the restaurant ID, and the rating.
Write a SQL query to find the average rating for each restaurant for each month. Only include restaurants with at least 2 reviews in a given month.
Example Input:
| review_id | user_id | submit_date | restaurant_id | rating |
|---|---|---|---|---|
| 1001 | 501 | 2022-01-15 | 101 | 4 |
| 1002 | 502 | 2022-01-20 | 101 | 5 |
| 1003 | 503 | 2022-01-25 | 102 | 3 |
| 1004 | 504 | 2022-02-15 | 102 | 4 |
| 1005 | 505 | 2022-02-20 | 101 | 5 |
| 1006 | 506 | 2022-03-01 | 101 | 4 |
| 1007 | 507 | 2022-03-05 | 102 | 2 |
Example Output:
| month | restaurant_id | avg_rating |
|---|---|---|
| 1 | 101 | 4.50 |
| 2 | 101 | 5.00 |
| 3 | 101 | 4.00 |
Answer:
This query groups the reviews by month and restaurant, and calculates average ratings. It filters out restaurants with less than two reviews in a specific month. To avoid including restaurants which had less that 2 reviews in any given month, the HAVING clause is used after the GROUP BY clause to filter on the results of the aggregation. The DATE_PART function extracts the month part from the submit_date.
To practice a similar window function question on DataLemur's free interactive SQL code editor, try this Google SQL Interview Question:
SQL Question 3: Design a Database Schema for Zomato
Zomato is a popular food delivery app joining the worldwide trend. You are tasked to design and implement an SQL database for this company. In particular, you will need to model tables for restaurants, customers and orders.
- Restaurants have a unique ID, name, type of cuisine they serve, and their location.
- Customers have a unique ID, name, contact, and their address.
- Orders are made by customers to a restaurant and have a unique ID, the date the order was placed, the customer's ID, the restaurant's ID and the total cost.
The management team wants to know how many orders each restaurant has received in a given month for tracking.
Assume that we are interested in the data for the month of August, 2022.
Sample data for the Restaurants table
| restaurant_id | restaurant_name | cuisine_type | location |
|---|---|---|---|
| 101 | Pasta Bella | Italian | Delhi |
| 102 | Sushi Ya | Japanese | Gurgaon |
| 103 | Burger King | Fast Food | Mumbai |
| 104 | Saibei Express | Chinese | Pune |
Sample data for the Customers table
| customer_id | customer_name | contact | address |
|---|---|---|---|
| 201 | John Doe | 9999999999 | Delhi |
| 202 | Jane Doe | 8888888888 | Gurgaon |
| 203 | Rob Stark | 7777777777 | Mumbai |
| 204 | Rachael Watson | 6666666666 | Pune |
Sample data for the Orders table
| order_id | order_date | customer_id | restaurant_id | total_order_cost |
|---|---|---|---|---|
| 1001 | 08/10/2022 | 201 | 101 | 500 |
| 1002 | 08/15/2022 | 202 | 102 | 650 |
| 1003 | 08/20/2022 | 203 | 103 | 450 |
| 1004 | 08/25/2022 | 204 | 104 | 550 |
| 1005 | 08/20/2022 | 201 | 103 | 600 |
| 1006 | 08/25/2022 | 202 | 102 | 500 |
Answer:
In this case, we can join the Orders and Restaurants table on restaurant_id to get the count of orders for each restaurant for the month of August, 2022. The SQL query would be:
Note: SQL code could vary slightly depending on the SQL dialect being used.
This query will output the name of each restaurant and the number of orders they received in the month of August 2022. It groups the orders by restaurant and only includes orders from August 2022.
SQL Question 4: What is the purpose of a primary key in a database?
A primary key is a column or group of columns that uniquely identifies a row in a table. For example, say you had a database of Zomato marketing campaigns data:
In this Zomato example, the CampaignID column is the primary key of the MarketingCampaigns table. The constraint ensures that no two rows have the same CampaignID. This helps to maintain the integrity of the data in the table by preventing duplicate rows.
SQL Question 5: Analyze Customer Behavior on Zomato
Given a database of Zomato customer behavior (orders and reviews), write a query to filter out customer records that meet the following conditions:
- Customers who have ordered more than 5 times in the last month
- Have given at least one 1-star review
- Their most ordered food item is 'Pizza'.
Hint: You can use WHERE, AND, OR and NOT in your SQL queries.
Below are the sample tables:
Example Input:
| order_id | customer_id | order_date | food_item |
|---|---|---|---|
| 1 | 1 | 08/05/2022 | Pizza |
| 2 | 2 | 08/05/2022 | Burger |
| 3 | 1 | 08/04/2022 | Pizza |
| 4 | 1 | 08/01/2022 | Pizza |
| 5 | 2 | 07/31/2022 | Pizza |
| 6 | 1 | 07/31/2022 | Burger |
| 7 | 1 | 07/27/2022 | Pizza |
| 8 | 1 | 07/24/2022 | Pizza |
Example Input:
| review_id | customer_id | review_date | rating |
|---|---|---|---|
| 1 | 1 | 08/04/2022 | 4 |
| 2 | 2 | 08/03/2022 | 3 |
| 3 | 1 | 08/01/2022 | 1 |
| 4 | 1 | 07/27/2022 | 5 |
| 5 | 2 | 07/25/2022 | 4 |
| 6 | 1 | 07/24/2022 | 1 |
Answer:
This query first filters out the customers who have given at least one 1-star review in the last month from the table, then from the table it finds customers who ordered more than 5 times with 'Pizza' as the food item. Finally, by doing an INNER JOIN between the two results, it gives out the demanded customer records.
SQL Question 6: Can you describe the concept of a database index and the various types of indexes?
A database index is a data structure that provides a quick lookup of data in a column or columns of a table.
There are several types of indexes that can be used in a database:
- Primary index: a unique identifier is used to access the row directly.
- Unique index: used to enforce the uniqueness of the indexed columns in a table.
- Composite index: created on multiple columns of a table, is used to speed up the search process for multiple columns
- Clustered index: determines the physical order of the data in a table
SQL Question 7: Calculate the average rating of restaurants in Zomato
You are given a database of restaurant ratings in Zomato, you must write a query that finds the average rating of every restaurant in the database for the previous month.
Example Input:
| rating_id | submit_date | restaurant_id | rating |
|---|---|---|---|
| 101 | 08/01/2022 00:00:00 | R1001 | 4.5 |
| 102 | 08/02/2022 00:00:00 | R2002 | 5.0 |
| 103 | 08/30/2022 00:00:00 | R1001 | 3.5 |
| 104 | 08/25/2022 00:00:00 | R2002 | 4.0 |
| 105 | 08/29/2022 00:00:00 | R1001 | 4.0 |
Example Output:
| restaurant | avg_rating |
|---|---|
| R1001 | 4.0 |
| R2002 | 4.5 |
Answer:
This query calculates average rating for each restaurant for the previous month. The WHERE clause filters records from the previous month considering both month and year. The GROUP BY clause groups the records by restaurant_id for calculating average. The AVG function calculates the average rating per restaurant. The result is returned as the restaurant ID and its average rating.
To practice a very similar question try this interactive Amazon Average Review Ratings Question which is similar for calculating averages for certain categories or this Facebook Active User Retention Question which is similar for time-based calculations.
SQL Question 8: What are the similarities and differences between a clustered index and non-clustered index?
Clustered indexes have a special characteristic in that the order of the rows in the database corresponds to the order of the rows in the index. This is why a table can only have one clustered index, but it can have multiple non-clustered indexes.
The main difference between clustered and non-clustered indexes is that the database tries to maintain the order of the data in the database to match the order of the corresponding keys in the clustered index. This can improve query performance as it provides a linear-access path to the data stored in the database.
SQL Question 9: Calculate Click-through-rate for Zomato's Digital Ads
Zomato, as a food delivery and restaurant discovery platform, frequently runs digital ad campaigns to attract and engage users. One key performance metric it tracks is click-through-rate (CTR), defined as the number of users who clicked on an ad divided by the number of users who viewed the ad.
Provided are two tables, and . The table logs each time an ad is viewed by a user, and the table logs each time an ad is clicked by a user.
Example Input
| time_stamp | user_id | ad_id |
|---|---|---|
| 01/15/2022 8:00:00 | 1012 | 40037 |
| 01/18/2022 19:10:00 | 7890 | 50941 |
| 02/04/2022 22:30:00 | 6543 | 50941 |
| 02/11/2022 13:00:00 | 1012 | 40037 |
| 02/15/2022 07:00:00 | 7890 | 50941 |
Example Input
| time_stamp | user_id | ad_id |
|---|---|---|
| 01/18/2022 19:11:00 | 7890 | 50941 |
| 01/15/2022 8:01:00 | 1012 | 40037 |
| 02/15/2022 07:01:00 | 7890 | 50941 |
| 02/17/2022 8:00:00 | 1212 | 50941 |
| 02/11/2022 13:01:00 | 1012 | 40037 |
Write an SQL query to find the click-through-rate on each ad.
Answer:
This SQL query first calculates the total views and clicks each ad has received in the and tables respectively. Then it joins these two derived tables on . The click-through-rate for each ad is then calculated as the ratio of total clicks to total views.
To solve a related problem on DataLemur's free online SQL code editor, solve this Facebook SQL Interview question:
SQL Question 10: Find the Most Popular Cuisine in Each City
Zomato, an online food order and delivery company, wants to keep track of the food preferences of its customers in various cities. The company wants to find out the most popular cuisine in each city based on the number of orders. Write a SQL query to find out the most popular cuisine in each city using the order and restaurant information.
Assume we have two tables - and .
Example Input:
| order_id | user_id | order_date | restaurant_id |
|---|---|---|---|
| 1001 | 123 | 07/20/2022 00:00:00 | 2001 |
| 1002 | 256 | 07/25/2022 00:00:00 | 2002 |
| 1003 | 789 | 07/27/2022 00:00:00 | 2003 |
| 1004 | 456 | 08/01/2022 00:00:00 | 2004 |
| 1005 | 123 | 08/05/2022 00:00:00 | 2002 |
Example Input:
| restaurant_id | city | cuisine_type |
|---|---|---|
| 2001 | Delhi | Italian |
| 2002 | Delhi | Chinese |
| 2003 | Mumbai | Indian |
| 2004 | Mumbai | Chinese |
| 2002 | Delhi | Chinese |
Example Output:
| city | most_popular_cuisine |
|---|---|
| Delhi | Chinese |
| Mumbai | Chinese |
Answer:
This SQL query first groups by restaurant_id in the Orders table to get the total number of orders for each restaurant, then joins this result with the Restaurants table based on restaurant_id. The final result is grouped by city and cuisine_type to show the most popular cuisine in each city, which is the cuisine with the highest total number of orders.
SQL Question 11: What is the purpose of the SQL constraint ?
A UNIQUE constraint ensures that all values in a column are different. It is often used in conjunction with other constraints, such as NOT NULL, to ensure that the data meets certain conditions.
For example, if you had Zomato sales leads data stored in a database, here's some constraints you'd use:
In this example, the UNIQUE constraint is applied to the "email" and "phone" fields to ensure that each Zomato lead has a unique email address and phone number. This helps to ensure the integrity of the data in the database and prevents errors that could occur if two leads had the same email address or phone number.
Preparing For The Zomato SQL Interview
Assuming that you've already got basic SQL skills, the next best tip we have to prepare for the Zomato SQL interview is to explore Zomato data in SQL, and also solve as many practice SQL interview questions as you can!
Besides solving the above Zomato SQL interview questions, you should also solve the 200+ SQL coding questions which come from companies like Facebook, Google, and VC-backed startups.
Each interview question has hints to guide you, detailed solutions and crucially, there's an online SQL coding environment so you can easily right in the browser your SQL query answer and have it graded.
To prep for the Zomato SQL interview you can also be useful to solve SQL questions from other tech companies like:
But if your SQL coding skills are weak, don't worry about jumping right into solving questions – go learn SQL with this interactive SQL tutorial.
This tutorial covers SQL topics like CASE/WHEN/ELSE statements and filtering data with boolean operators – both of which show up routinely in SQL job interviews at Zomato.
Zomato Data Science Interview Tips
What Do Zomato Data Science Interviews Cover?
Beyond writing SQL queries, the other question categories tested in the Zomato Data Science Interview are:
- Statistics Interview Questions
- Python Pandas or R Coding Questions
- Product Metrics Interview Questions
- ML Interview Questions
- Behavioral Interview Questions
How To Prepare for Zomato Data Science Interviews?
The best way to prepare for Zomato Data Science interviews is by reading Ace the Data Science Interview. The book's got:
- 201 Interview Questions from FAANG, tech startups, and Wall Street
- A Crash Course on SQL, Product-Sense & ML
- Amazing Reviews (900+ reviews, 4.5-star rating)