January 13, 2026

Mastering Advanced SQL Interview Questions

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 email groups duplicates

  • ROW_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 DESC

  • Lowest 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 JOIN keeps all customers

  • COALESCE converts NULL to 0

💡 Interview Tip

If they say “include even if no records exist”, think LEFT JOIN.

🧠 Key SQL Patterns to Remember

Problem TypePattern
Delete duplicatesROW_NUMBER()
Worked on all itemsGROUP BY + HAVING
Top N per groupRANK() / DENSE_RANK()
Compare with averageSubquery
Percentile / top %PERCENT_RANK()
Include missing dataLEFT 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


By
Labels: