logo

10 Zomato SQL Interview Questions (Updated 2024)

Updated on

March 1, 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 10 Zomato SQL interview questions – able to answer them all?

10 Zomato SQL Interview Questions

SQL Question 1: 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_iduser_idsubmit_daterestaurant_idrating
10015012022-01-151014
10025022022-01-201015
10035032022-01-251023
10045042022-02-151024
10055052022-02-201015
10065062022-03-011014
10075072022-03-051022
Example Output:
monthrestaurant_idavg_rating
11014.50
21015.00
31014.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: Google SQL Interview Question

SQL Question 2: 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.

  1. Restaurants have a unique ID, name, type of cuisine they serve, and their location.
  2. Customers have a unique ID, name, contact, and their address.
  3. 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_idrestaurant_namecuisine_typelocation
101Pasta BellaItalianDelhi
102Sushi YaJapaneseGurgaon
103Burger KingFast FoodMumbai
104Saibei ExpressChinesePune

Sample data for the Customers table

customer_idcustomer_namecontactaddress
201John Doe9999999999Delhi
202Jane Doe8888888888Gurgaon
203Rob Stark7777777777Mumbai
204Rachael Watson6666666666Pune

Sample data for the Orders table

order_idorder_datecustomer_idrestaurant_idtotal_order_cost
100108/10/2022201101500
100208/15/2022202102650
100308/20/2022203103450
100408/25/2022204104550
100508/20/2022201103600
100608/25/2022202102500

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 3: 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.

Zomato SQL Interview Questions

SQL Question 4: 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_idcustomer_idorder_datefood_item
1108/05/2022Pizza
2208/05/2022Burger
3108/04/2022Pizza
4108/01/2022Pizza
5207/31/2022Pizza
6107/31/2022Burger
7107/27/2022Pizza
8107/24/2022Pizza
Example Input:
review_idcustomer_idreview_daterating
1108/04/20224
2208/03/20223
3108/01/20221
4107/27/20225
5207/25/20224
6107/24/20221

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 5: 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:

  1. Primary index: a unique identifier is used to access the row directly.
  2. Unique index: used to enforce the uniqueness of the indexed columns in a table.
  3. Composite index: created on multiple columns of a table, is used to speed up the search process for multiple columns
  4. Clustered index: determines the physical order of the data in a table

SQL Question 6: 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_idsubmit_daterestaurant_idrating
10108/01/2022 00:00:00R10014.5
10208/02/2022 00:00:00R20025.0
10308/30/2022 00:00:00R10013.5
10408/25/2022 00:00:00R20024.0
10508/29/2022 00:00:00R10014.0
Example Output:
restaurantavg_rating
R10014.0
R20024.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 7: 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 8: 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_stampuser_idad_id
01/15/2022 8:00:00101240037
01/18/2022 19:10:00789050941
02/04/2022 22:30:00654350941
02/11/2022 13:00:00101240037
02/15/2022 07:00:00789050941
Example Input
time_stampuser_idad_id
01/18/2022 19:11:00789050941
01/15/2022 8:01:00101240037
02/15/2022 07:01:00789050941
02/17/2022 8:00:00121250941
02/11/2022 13:01:00101240037

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: Facebook App CTR SQL Interview question

SQL Question 9: 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_iduser_idorder_daterestaurant_id
100112307/20/2022 00:00:002001
100225607/25/2022 00:00:002002
100378907/27/2022 00:00:002003
100445608/01/2022 00:00:002004
100512308/05/2022 00:00:002002
Example Input:
restaurant_idcitycuisine_type
2001DelhiItalian
2002DelhiChinese
2003MumbaiIndian
2004MumbaiChinese
2002DelhiChinese
Example Output:
citymost_popular_cuisine
DelhiChinese
MumbaiChinese

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 10: What is the purpose of the SQL constraint ?

{#Question-10}

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. DataLemur SQL and Data Science Interview Questions

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.

DataLemur SQL Tutorial for Data Science

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:

Zomato Data Scientist

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)

Acing Data Science Interview