Top SQL Interview Questions for Blinkit & Zepto (2+ Years)

1️⃣ How do you optimize SQL queries for large datasets?

2️⃣ Difference between CTE and Subquery?

3️⃣ PARTITION BY vs. GROUP BY – When to use?

4️⃣ Write a query to find repeat customers.

5️⃣ Explain Dense_Rank(), Rank(), and Row_Number().

6️⃣ How to handle NULL values in JOIN operations?

7️⃣ How to calculate running totals in SQL?

8️⃣ Write SQL to find month-over-month growth.

9️⃣ What are indexing strategies for better performance?

1️⃣0️⃣ Difference between UNION and UNION ALL?

You Should Know:

1. Optimizing SQL Queries for Large Datasets

• Use indexing on columns frequently used in WHERE clauses.
• Avoid SELECT *; specify only the required columns.
• Use `EXPLAIN` to analyze query performance.
• Example:

  CREATE INDEX idx_customer_id ON orders(customer_id);
  EXPLAIN SELECT customer_id, order_date FROM orders WHERE customer_id = 123;
  

2. CTE vs. Subquery

• CTE (Common Table Expression): Improves readability and can be referenced multiple times.

  WITH CTE AS (
  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id
  )
  SELECT * FROM CTE WHERE total_spent > 1000;
  

• Subquery: Embedded within another query.

  SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE customer_id = c.id) AS order_count
  FROM customers c;
  

3. PARTITION BY vs. GROUP BY

• PARTITION BY: Used in window functions to partition data without reducing rows.

  SELECT customer_id, order_date, 
  SUM(amount) OVER (PARTITION BY customer_id) AS total_spent
  FROM orders;
  

• GROUP BY: Aggregates data into summary rows.

  SELECT customer_id, SUM(amount) AS total_spent
  FROM orders
  GROUP BY customer_id;
  

4. Finding Repeat Customers

SELECT customer_id, COUNT(<em>) AS order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(</em>) > 1;

5. Dense_Rank(), Rank(), and Row_Number()

• Dense_Rank(): Assigns ranks without gaps.

  SELECT customer_id, DENSE_RANK() OVER (ORDER BY total_spent DESC) AS rank
  FROM (SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id);
  

• Rank(): Assigns ranks with gaps.
• Row_Number(): Assigns unique sequential integers.

6. Handling NULL Values in JOINs

• Use `COALESCE` to replace NULLs.

  SELECT a.id, COALESCE(b.amount, 0) AS amount
  FROM table_a a
  LEFT JOIN table_b b ON a.id = b.id;
  

7. Calculating Running Totals

SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

8. Month-over-Month Growth

WITH monthly_sales AS (
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(amount) AS total_sales
FROM orders
GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT month, total_sales,
(total_sales - LAG(total_sales, 1) OVER (ORDER BY month)) / LAG(total_sales, 1) OVER (ORDER BY month) * 100 AS growth_rate
FROM monthly_sales;

9. Indexing Strategies

• Use composite indexes for multi-column queries.
• Avoid over-indexing; it can slow down write operations.

10. UNION vs. UNION ALL

• UNION: Removes duplicates.
• UNION ALL: Includes duplicates.

  SELECT customer_id FROM orders
  UNION
  SELECT customer_id FROM returns;
  

What Undercode Say:

Mastering SQL is essential for data-driven roles, especially in product-based companies like Blinkit and Zepto. Practice these concepts using real datasets and tools like MySQL, PostgreSQL, or SQLite. For further learning, explore platforms like LeetCode or HackerRank. Keep experimenting with queries and optimizing them for better performance. Happy querying!

References:

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

Join Our Cyber World:

💬 Whatsapp | 💬 Telegram