🎯 Crack SDE Interviews with These Must-Know SQL Query Problems

✅ 1. Find the Second Highest Salary

Table: Employee(id, name, salary)

SELECT MAX(salary) AS SecondHighestSalary
FROM Employee
WHERE salary < (
    SELECT MAX(salary) FROM Employee
);

🧠 Concept: Subqueries, MAX()
🎯 Asked by: Amazon, Google, Meta

---

✅ 2. Find Duplicate Records

Table: Users(id, email)

SELECT email, COUNT(*) AS cnt
FROM Users
GROUP BY email
HAVING COUNT(*) > 1;

🧠 Concept: GROUP BY, HAVING
🎯 Asked by: Microsoft, Uber

---

✅ 3. Find Employees Who Earn More Than Their Manager

Table: Employee(id, name, salary, manager_id)

SELECT e.name
FROM Employee e
JOIN Employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;

🧠 Concept: Self JOIN
🎯 Asked by: Google, Oracle

---

✅ 4. Nth Highest Salary Using DENSE_RANK

Table: Employee(id, name, salary)

SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM Employee
) AS ranked
WHERE rnk = 3;

🧠 Concept: Window Functions
🎯 Asked by: Flipkart, Salesforce

---

✅ 5. Find Customers with No Orders

Tables: Customers(customer_id), Orders(order_id, customer_id)

SELECT c.customer_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

🧠 Concept: LEFT JOIN, NULL Filtering
🎯 Asked by: Amazon, Adobe

---

✅ 6. Get Department with Highest Average Salary

Table: Employee(id, name, salary, department_id)

SELECT department_id
FROM Employee
GROUP BY department_id
ORDER BY AVG(salary) DESC
LIMIT 1;

🧠 Concept: Aggregation, ORDER BY, LIMIT
🎯 Asked by: Google, Paytm

---

✅ 7. Find Consecutive Login Days

Table: Login(user_id, login_date)
Find users who logged in 3 consecutive days.

SELECT DISTINCT l1.user_id
FROM Login l1
JOIN Login l2 ON l1.user_id = l2.user_id AND DATEDIFF(l2.login_date, l1.login_date) = 1
JOIN Login l3 ON l1.user_id = l3.user_id AND DATEDIFF(l3.login_date, l1.login_date) = 2;

🧠 Concept: Self JOINs with date difference
🎯 Asked by: Amazon, Microsoft

---

✅ 8. Rank Employees within Each Department

Table: Employee(id, name, salary, department_id)

SELECT id, name, salary, department_id,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM Employee;

🧠 Concept: Partitioned Window Functions
🎯 Asked by: Google, Oracle

---

✅ 9. Get Cumulative Salary

Table: Employee(id, name, salary)

SELECT name, salary,
       SUM(salary) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_salary
FROM Employee;

🧠 Concept: Window Function with SUM
🎯 Asked by: Goldman Sachs, Morgan Stanley

---

✅ 10. Find Customers Who Ordered All Products

Tables: Orders(customer_id, product_id), Products(product_id)

SELECT customer_id
FROM Orders
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM Products);

🧠 Concept: COUNT DISTINCT, Subqueries
🎯 Asked by: Amazon, Swiggy

---