A Practical Guide for Freshers & Experienced Engineers
SQL interviews are not about memorizing syntax — they test data thinking, edge-case handling, and real-world querying skills.
This blog covers frequently asked SQL interview problems, explained step-by-step with:
clear intent
correct SQL
beginner-friendly explanations
advanced variations for experienced candidates
You can revisit this blog anytime — it’s written for long-term learning.
1️⃣ Delete Duplicate Records While Keeping One
📌 Problem
A table contains duplicate rows. You need to delete duplicates but keep one record per group.
Assume a table:
Employees(id, email)
✅ Solution Using ROW_NUMBER() (Best Practice)
DELETE FROM Employees
WHERE id IN (
SELECT id
FROM (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM Employees
) t
WHERE rn > 1
);
🧠 Explanation
PARTITION BY emailgroups duplicatesROW_NUMBER()assigns 1, 2, 3…Keep
rn = 1, delete the rest
💡 Interview Tip
Always preview with SELECT before DELETE.
2️⃣ Find Employees Who Worked on All Projects
Tables:
Employees(emp_id)
EmployeeProjects(emp_id, project_id)
Projects(project_id)
✅ Solution Using GROUP BY + HAVING
SELECT emp_id
FROM EmployeeProjects
GROUP BY emp_id
HAVING COUNT(DISTINCT project_id) =
(SELECT COUNT(*) FROM Projects);
🧠 Explanation
Count projects per employee
Compare with total project count
💡 Interview Tip
This pattern = “worked on all” / “matched all” → remember it.
3️⃣ Customers With Most Orders but Lowest Total Spend (Last Month)
Table:
Orders(order_id, customer_id, amount, order_date)
✅ Step 1: Aggregate Last Month Data
WITH last_month_orders AS (
SELECT customer_id,
COUNT(*) AS order_count,
SUM(amount) AS total_amount
FROM Orders
WHERE order_date >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
AND order_date < DATE_TRUNC('month', CURRENT_DATE)
GROUP BY customer_id
)
SELECT *
FROM last_month_orders
ORDER BY order_count DESC, total_amount ASC;
🧠 Explanation
Highest orders →
order_count DESCLowest spend →
total_amount ASC
💡 Interview Tip
Ordering by multiple business conditions is very common.
4️⃣ Products With Sales Higher Than Average Monthly Sales
Tables:
Sales(product_id, sale_amount, sale_date)
Products(product_id, product_name)
✅ Using Subquery + JOIN
SELECT p.product_id, p.product_name
FROM Products p
JOIN (
SELECT product_id,
AVG(sale_amount) AS avg_sales
FROM Sales
GROUP BY product_id
) ps ON p.product_id = ps.product_id
WHERE ps.avg_sales >
(SELECT AVG(sale_amount) FROM Sales);
🧠 Explanation
Inner query → avg per product
Subquery → global avg
Compare both
💡 Interview Tip
Interviewers love “compare against average” questions.
5️⃣ Students in Top 10% of Their Class (Window Functions)
Table:
Students(student_id, class_id, marks)
✅ Using PERCENT_RANK()
SELECT student_id, class_id, marks
FROM (
SELECT student_id,
class_id,
marks,
PERCENT_RANK() OVER (PARTITION BY class_id ORDER BY marks DESC) AS pr
FROM Students
) t
WHERE pr <= 0.10;
🧠 Explanation
PERCENT_RANK()gives percentile<= 0.10→ top 10%
💡 Interview Tip
For rank-based questions, always think window functions.
6️⃣ Suppliers With Products Cheaper Than Category Average
(Correlated Subquery + JOIN)
Tables:
Suppliers(supplier_id, name)
Products(product_id, supplier_id, category_id, price)
✅ Solution
SELECT DISTINCT s.supplier_id, s.name
FROM Suppliers s
JOIN Products p ON s.supplier_id = p.supplier_id
WHERE p.price <
(
SELECT AVG(p2.price)
FROM Products p2
WHERE p2.category_id = p.category_id
);
🧠 Explanation
Subquery recalculates avg per category
Compares product price with category avg
💡 Interview Tip
This is a classic correlated subquery example.
7️⃣ Customers and Their Total Order Amount
(Include Customers With No Orders)
Tables:
Orders(order_id, customer_id, amount)
Customers(customer_id, name)
✅ Correct Solution Using LEFT JOIN
SELECT c.customer_id,
c.name,
COALESCE(SUM(o.amount), 0) AS total_order_amount
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;
🧠 Explanation
LEFT JOINkeeps all customersCOALESCEconverts NULL to 0
💡 Interview Tip
If they say “include even if no records exist”, think LEFT JOIN.
🧠 Key SQL Patterns to Remember
| Problem Type | Pattern |
|---|---|
| Delete duplicates | ROW_NUMBER() |
| Worked on all items | GROUP BY + HAVING |
| Top N per group | RANK() / DENSE_RANK() |
| Compare with average | Subquery |
| Percentile / top % | PERCENT_RANK() |
| Include missing data | LEFT JOIN |
🎯 What Interviewers Really Look For
✔ Correct joins
✔ Proper grouping
✔ No missing edge cases
✔ Business logic clarity
✔ Clean, readable SQL
Not just syntax.
📌 Final Advice for Long-Term SQL Mastery
Always start with SELECT, then DELETE/UPDATE
Think in sets, not rows
Ask clarifying questions (
> vs >=, date ranges)Practice explaining why, not just how
