SQL Interview Question at Zomato: These questions were recently asked in interview at Zomato, you must master these. read more
SQL Interview Question at Zomato
1. Write a SQL query to find the top 5 customers with the highest total order value in the last month.
SELECT
date_format(order_date, '%Y-%m') as month,
customer_id,
SUM(total_amount) AS total_sale
FROM Orders
WHERE DATE_FORMAT(order_date, '%Y-%m') = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 1 MONTH), '%Y-%m')
GROUP BY month, customer_id
ORDER BY total_sale DESC
LIMIT 5;https://onecompiler.com/mysql/438qdug79
2. How do you remove duplicate records from a table?
WITH CTE AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY customer_id, restaurant_id, order_date ORDER BY review_id) AS rn
FROM Reviews
)
DELETE FROM Reviews
WHERE review_id IN (SELECT review_id FROM CTE WHERE rn > 1);https://onecompiler.com/mysql/438tfb979
For this question, I am using the Reviews table. I define a duplicate as a case where a customer has more than one review for the same restaurant on the same order date. Any subsequent review beyond the first one will be considered a duplicate.
To identify duplicates, I used a ROW_NUMBER() function inside a CTE. This assigns a unique row number to each review, grouped by customer_id, restaurant_id, and order_date. If a row has a row number greater than 1, it means it’s a duplicate.
In the DELETE statement, I removed these duplicates by filtering out records where the review_id exists in the list of duplicate records identified in the CTE.
3. Write a query to calculate the average order value per restaurant.
SELECT
restaurant_id,
round(avg(total_amount),2) as avg_order_value
FROM Orders
GROUP BY restaurant_id;https://onecompiler.com/mysql/438wdrdeq
4. Retrieve the top 3 most ordered dishes from the past 6 months.
SELECT
oi.dish_id,
COUNT(*) AS num_order
FROM Orders o
INNER JOIN Order_Items oi USING(order_id)
WHERE o.order_date BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 MONTH) AND CURDATE()
GROUP BY oi.dish_id
ORDER BY num_order DESC
LIMIT 3;https://onecompiler.com/mysql/438we2vy2
5. Explain the difference between LEFT JOIN, RIGHT JOIN, and INNER JOIN with examples.
The join in SQL are used to combine data from two or more tables, based on the requirement we can use left, right or inner join.
LEFT JOIN – Returns all data from left table and matching data from right table unmatched rows will from left table will be included with NULLs.
RIGHT JOIN – Returns all data from RIGHT table with matching data from LEFT table and unmatched rows from Right table will be included with NULLs.
INNER JOIN – Returns all matching data from both tables.
example scenario: Imagin you two tables Customers and Orders
Customers Table
| customer_id | customer_name |
|---|---|
| 1 | Alice |
| 2 | Bob |
| 3 | Charlie |
| 4 | David |
Orders Table
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-01-10 | 500 |
| 102 | 2 | 2024-01-12 | 750 |
| 103 | 1 | 2024-02-01 | 300 |
| 104 | 3 | 2024-02-15 | 450 |
Left Join
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;Result:
| customer_id | customer_name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 500 |
| 1 | Alice | 103 | 300 |
| 2 | Bob | 102 | 750 |
| 3 | Charlie | 104 | 450 |
| 4 | David | NULL | NULL |
It includes all rows from customers table and matching records from orders table.
Right Join
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM Customers c
RIGHT JOIN Orders o ON c.customer_id = o.customer_id;Result:
| customer_id | customer_name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 500 |
| 1 | Alice | 103 | 300 |
| 2 | Bob | 102 | 750 |
| 3 | Charlie | 104 | 450 |
It includes all rows from order table and matching records from customers table.
Inner Join
SELECT c.customer_id, c.customer_name, o.order_id, o.amount
FROM Customers c
INNER JOIN Orders o ON c.customer_id = o.customer_id;Result:
| customer_id | customer_name | order_id | amount |
|---|---|---|---|
| 1 | Alice | 101 | 500 |
| 1 | Alice | 103 | 300 |
| 2 | Bob | 102 | 750 |
| 3 | Charlie | 104 | 450 |
It includes all matching rows from both customers and orders table.
6. How do you identify and handle missing values in SQL?
Missing values are represented as NULL in SQL, to find missing value s we can use ISNULL or COUNT() functions. Once identified we can eitgher remove the rows with missing value order we can ignore them in aggregations or we can fill them using a defaut value or using windows functions.
7. Write a query to rank restaurants based on total revenue using window functions.
with revenue as
(SELECT restaurant_id, sum(total_amount) as total_rev
FROM Orders
GROUP BY restaurant_id)
SELECT *,
dense_rank() over(order by total_rev desc) as rnk
FROM revenue;https://onecompiler.com/mysql/438wjxzvu
8. Explain GROUP BY vs PARTITION BY in SQL.
GROUP BY groups data into single rows based on the specified columns while performing aggregation.PARTITION BY performs the aggregation across partitions without collapsing the rows, retaining the original dataset structure.
| Aspect | GROUP BY | PARTITION BY |
|---|---|---|
| Purpose | Aggregates data into grouped rows. | Divides data into partitions while retaining individual rows. |
| Result Structure | Reduces the result set to one row per group. | Maintains all rows while applying calculations within partitions. |
| Use Case | Summarizing data (e.g., total sales per region). | Performing window calculations (e.g., running totals or ranks). |
| Clause Type | Used with aggregate functions and SELECT. | Used with window functions like ROW_NUMBER(), RANK(), SUM(). |
| Visibility | Appears directly in the query result. | Works behind the scenes without altering row count. |
9. How would you find customers who have placed orders in January but not in February?
SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING
SUM(MONTH(order_date) = 1) > 0 AND
SUM(MONTH(order_date) = 2) = 0;we group by customer_id and apply the SUM(MONTH(order_date) = x) logic.
The expression MONTH(order_date) = 1 returns 1 if TRUE, else 0.
| customer_id | Jan Orders (SUM(MONTH(order_date)=1)) | Feb Orders (SUM(MONTH(order_date)=2)) |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 1 |
| 3 | 1 | 0 |
| 4 | 1 | 0 |
| 5 | 1 | 0 |
This query counts the orders for each customer in January and February. It selects the customer_id when:
- January Orders:
SUM(MONTH(order_date) = 1) > 0- This ensures the customer placed at least one order in January.
- February Orders:
SUM(MONTH(order_date) = 2) = 0- This ensures the customer placed no orders in February.
So, if the sum for January is greater than 0, it indicates orders were placed in January.
If the sum for February is 0, it indicates no orders were placed in February.
The result is a list of customers who ordered in January but not in February.
10. How do you optimize a slow SQL query?
There are many things that can be done to optimize a slow query, some common options are using index, avoid selecting unnecessary columns, filtering early like using where instead of having, using limit clause, use of CTEs and temp tables.
For More SQL Interview Question at Zomato Follow Us On:
Thank you for reading! We hope this article has been helpful in your SQL Interview Questions preparation. If you found it valuable, please share it with your network.
Also Read:-
- The Ultimate Guide to SQL Indexing and Query Optimization
- SQL Interview Questions Asked In Walmart For Data Analyst Post | CTC – 18 LPA | Learn With Curious Club!!
- SQL Interview Questions for Deloitte Data Engineer Roles: Your Ultimate Prep Guide
- Data Analyst SQL Interview Questions | EY (Ernst & Young) | Shared By An Experienced Data Analyst
- 1164 Product Price at a Given Date
SQL Interview Question at Zomato, SQL Interview Question at Zomato, SQL Interview Question at Zomato
Hi, I am Vishal Jaiswal, I have about a decade of experience of working in MNCs like Genpact, Savista, Ingenious. Currently i am working in EXL as a senior quality analyst. Using my writing skills i want to share the experience i have gained and help as many as i can.