Top SQL Interview Questions for Zomato (2+ Years)

1. How to find the most ordered dish in the last 30 days?
2. Write a query to calculate the average delivery time by city.
3. How do you handle duplicate orders in data?

4. Difference between RANK(), DENSE_RANK(), and ROW_NUMBER().

1. How to calculate daily active users (DAU) in SQL?
2. How to find customers who haven’t ordered in the last 3 months?

7. How do you optimize slow-running queries?

8. Explain CTEs and when to use them.

1. What is the difference between WHERE and HAVING?

10. How to join multiple tables efficiently?

You Should Know:

1. Finding the Most Ordered Dish in the Last 30 Days

   SELECT dish_name, COUNT(*) AS order_count
   FROM orders
   WHERE order_date >= NOW() - INTERVAL '30 days'
   GROUP BY dish_name
   ORDER BY order_count DESC
   LIMIT 1;
   

2. Calculating Average Delivery Time by City

SELECT city, AVG(delivery_time) AS avg_delivery_time
FROM orders
GROUP BY city;

3. Handling Duplicate Orders

DELETE FROM orders
WHERE order_id IN (
SELECT order_id
FROM (
SELECT order_id, ROW_NUMBER() OVER (PARTITION BY customer_id, order_date ORDER BY order_id) AS rn
FROM orders
) t
WHERE t.rn > 1
);

4. Difference Between RANK(), DENSE_RANK(), and ROW_NUMBER()

• RANK(): Skips ranks if there are ties.
• DENSE_RANK(): Does not skip ranks.
• ROW_NUMBER(): Assigns a unique number to each row.

SELECT 
customer_id, 
RANK() OVER (ORDER BY total_spent DESC) AS rank,
DENSE_RANK() OVER (ORDER BY total_spent DESC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY total_spent DESC) AS row_num
FROM customers;

5. Calculating Daily Active Users (DAU)

SELECT DATE(order_date) AS day, COUNT(DISTINCT user_id) AS dau
FROM orders
GROUP BY day;

6. Finding Inactive Customers

SELECT customer_id
FROM customers
WHERE customer_id NOT IN (
SELECT DISTINCT customer_id
FROM orders
WHERE order_date >= NOW() - INTERVAL '3 months'
);

7. Optimizing Slow-Running Queries

• Use INDEXES on frequently queried columns.
• Avoid SELECT ; specify only required columns.
• Use EXPLAIN to analyze query performance.

CREATE INDEX idx_order_date ON orders(order_date);
EXPLAIN SELECT * FROM orders WHERE order_date >= '2023-01-01';

8. Common Table Expressions (CTEs)

WITH top_customers AS (
SELECT customer_id, SUM(total_spent) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 10
)
SELECT * FROM top_customers;

9. Difference Between WHERE and HAVING

• WHERE: Filters rows before grouping.
• HAVING: Filters rows after grouping.

SELECT city, AVG(delivery_time) AS avg_delivery_time
FROM orders
WHERE order_date >= '2023-01-01'
GROUP BY city
HAVING AVG(delivery_time) > 30;

10. Joining Multiple Tables Efficiently

SELECT o.order_id, c.customer_name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

What Undercode Say:

SQL is a powerful tool for data analysis and manipulation, especially in tech-driven industries like food delivery. Mastering these queries and concepts will not only help you ace interviews but also improve your day-to-day database management skills. Practice these commands on platforms like LeetCode or HackerRank to solidify your understanding. For further learning, check out SQL Tutorial and W3Schools SQL.

Remember, the key to mastering SQL is consistent practice and real-world application. Happy querying! 🚀

References:

Reported By: Sumit Yadav – Hackers Feeds
Extra Hub: Undercode MoN
Basic Verification: Pass ✅

Join Our Cyber World:

💬 Whatsapp | 💬 Telegram