Top SQL Interview Queries (With Logic & Real-World Scenarios)

Want to crack your next SQL interview with confidence? Here's a curated list of real-world SQL queries often asked by top companies like Amazon, Google, Flipkart, Infosys, and TCS.

What makes this list different?
✅ Focus on business logic, not just syntax
✅ Includes JOINs, Aggregations, Subqueries, Window Functions
✅ Interview-tested and beginner-friendly

---

1. Second Highest Salary

SELECT MAX(salary) 
FROM employees 
WHERE salary < (SELECT MAX(salary) FROM employees);

📌 Logic:
The inner query fetches the highest salary, the outer one gets the max salary less than that.

---

2. Find Duplicate Emails

SELECT email, COUNT(*) 
FROM users 
GROUP BY email 
HAVING COUNT(*) > 1;

📌 Logic:
Group by email and filter for those that appear more than once.

---

3. Top 3 Highest Paid Employees

SELECT * 
FROM employees 
ORDER BY salary DESC 
LIMIT 3;

---

4. Find Employees Without Managers

SELECT name 
FROM employees 
WHERE manager_id IS NULL;

---

5. Customer with Most Orders

SELECT customer_id, COUNT(*) AS order_count 
FROM orders 
GROUP BY customer_id 
ORDER BY order_count DESC 
LIMIT 1;

---

6. Department with Highest Average Salary

SELECT department, AVG(salary) AS avg_sal 
FROM employees 
GROUP BY department 
ORDER BY avg_sal DESC 
LIMIT 1;

---

7. Users Who Never Ordered

SELECT u.name 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
WHERE o.id IS NULL;

📌 Logic:
Use a LEFT JOIN to retain all users, then filter those without matching orders.

---

8. Employees and Their Managers (Self JOIN)

SELECT e.name AS employee, m.name AS manager 
FROM employees e 
LEFT JOIN employees m ON e.manager_id = m.id;

---

9. Employees in Multiple Departments

SELECT employee_id 
FROM employee_department 
GROUP BY employee_id 
HAVING COUNT(DISTINCT department_id) > 1;

---

10. Nth Highest Salary (Dynamic)

SELECT DISTINCT salary 
FROM employees e1 
WHERE N - 1 = (
  SELECT COUNT(DISTINCT salary) 
  FROM employees e2 
  WHERE e2.salary > e1.salary
);

📌 Tip: Replace N with 2, 3, 4 to get the respective rank.

---

11. Latest Order of Each Customer

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn 
  FROM orders
) t
WHERE rn = 1;

---

12. Monthly Revenue Report

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, 
       SUM(amount) AS total_revenue 
FROM orders 
GROUP BY DATE_FORMAT(order_date, '%Y-%m');

---

13. Count Orders Per Product

SELECT product_id, COUNT(*) AS order_count 
FROM order_items 
GROUP BY product_id;

---

14. Users With Consecutive Logins (Advanced)

SELECT user_id, login_date,
       LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_date
FROM logins
WHERE DATEDIFF(login_date, prev_date) = 1;

📌 Logic:
Use the LAG() window function to compare current and previous login dates.

---

These queries are more than just interview questions—they reflect practical database problems you'll solve on the job. Master the logic behind them, and you'll not only crack interviews but also become a stronger backend developer.

👉 Bookmark this blog for quick revision before your next interview!

---

👉 Found this helpful? Follow DevDaily for more developer blogs every week!

✍ Written by Rishabh Mishra