SQL Notes
Learn the most important SQL scenario-based interview questions with practical solutions, query logic, optimization techniques, and real-world database cases.
Why Scenario-Based Questions Matter
Entry-level interviews ask definitions: "What is a JOIN?" or "Explain normalization." That is fine for screening basic knowledge. But real developer roles — Software Engineer, Backend Developer, SQL Developer, Database Engineer, Data Analyst — test your ability to solve actual problems with SQL.
Interviewers present a scenario (a table structure and a business requirement) and ask you to write the query. They evaluate:
- Problem decomposition — Can you break a complex requirement into SQL operations?
- Query correctness — Does your query produce the right result for all edge cases?
- Optimization awareness — Do you know when your approach might be slow?
- Alternative approaches — Can you solve the same problem multiple ways?
Let us work through the most commonly asked scenarios with detailed explanations of WHY each solution works, not just the code.
Scenario 2: Find Nth Highest Salary (Generalized)
SELECT Salary FROM (
SELECT Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) AS Rnk
FROM Employees
) ranked
WHERE Rnk = N; -- Replace N with desired rankThis is the generalized version. Replace N with 3 for third highest, 5 for fifth highest, etc. This pattern appears in almost every SQL interview.
Scenario 3: Employees Earning More Than Department Average
SELECT E.EmployeeName, E.Salary, E.DepartmentID
FROM Employees E
WHERE Salary > (
SELECT AVG(Salary) FROM Employees
WHERE DepartmentID = E.DepartmentID
);This is a correlated subquery — the inner query references the outer query's row (E.DepartmentID). It executes once for each row in the outer query. For large tables, consider rewriting with a JOIN for better performance:
SELECT E.EmployeeName, E.Salary, E.DepartmentID
FROM Employees E
JOIN (
SELECT DepartmentID, AVG(Salary) AS AvgSalary
FROM Employees GROUP BY DepartmentID
) D ON E.DepartmentID = D.DepartmentID
WHERE E.Salary > D.AvgSalary;Scenario 4: Find Duplicate Records
SELECT Email, COUNT(*) AS Total
FROM Customers
GROUP BY Email
HAVING COUNT(*) > 1;Key concept: HAVING filters AFTER grouping (unlike WHERE which filters before). You cannot use WHERE COUNT(*) > 1 because aggregate functions are not available in WHERE clauses.
Scenario 5: Delete Duplicate Records (Keep One)
WITH Duplicates AS (
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Email ORDER BY CustomerID) AS RN
FROM Customers
)
DELETE FROM Duplicates WHERE RN > 1;Logic: ROW_NUMBER assigns 1, 2, 3... to each row within each Email group. Deleting rows where RN > 1 keeps only the first occurrence (smallest CustomerID) for each duplicate group.
Alternative for databases that do not support CTE deletion:
DELETE FROM Customers
WHERE CustomerID NOT IN (
SELECT MIN(CustomerID) FROM Customers GROUP BY Email
);Scenario 6: Find Records Without a Match (LEFT JOIN + NULL)
Employees without a department:
SELECT E.*
FROM Employees E
LEFT JOIN Departments D ON E.DepartmentID = D.DepartmentID
WHERE D.DepartmentID IS NULL;Departments without any employees:
SELECT D.*
FROM Departments D
LEFT JOIN Employees E ON D.DepartmentID = E.DepartmentID
WHERE E.EmployeeID IS NULL;Pattern: LEFT JOIN preserves all rows from the left table. Where there is no match, the right table columns are NULL. Filtering for NULL finds the unmatched rows. This is one of the most common interview patterns.
Scenario 7: Top N Records Per Group
Top 3 highest-paid employees in each department:
SELECT * FROM (
SELECT EmployeeName, Salary, DepartmentID,
DENSE_RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS Rnk
FROM Employees
) ranked
WHERE Rnk <= 3;PARTITION BY resets the ranking for each department. Without it, you would get the top 3 across the entire company.
Scenario 8: Customers Who Never Placed Orders
SELECT C.*
FROM Customers C
LEFT JOIN Orders O ON C.CustomerID = O.CustomerID
WHERE O.OrderID IS NULL;Alternative using NOT EXISTS (often faster for large tables):
SELECT * FROM Customers C
WHERE NOT EXISTS (
SELECT 1 FROM Orders O WHERE O.CustomerID = C.CustomerID
);Scenario 9: Running Total (Cumulative Sum)
SELECT EmployeeID, Salary,
SUM(Salary) OVER (ORDER BY EmployeeID) AS RunningTotal
FROM Employees;How it works: The window function computes SUM for all rows from the start up to and including the current row (the default frame). Each row's RunningTotal includes itself and all previous rows.
Scenario 10: Find Consecutive Login Days
This is an advanced interview classic. The trick is using ROW_NUMBER to identify streaks:
WITH LoginDates AS (
SELECT UserID, LoginDate,
LoginDate - INTERVAL ROW_NUMBER() OVER (
PARTITION BY UserID ORDER BY LoginDate
) DAY AS GroupID
FROM UserLogins
)
SELECT UserID, MIN(LoginDate) AS StreakStart,
MAX(LoginDate) AS StreakEnd,
COUNT(*) AS ConsecutiveDays
FROM LoginDates
GROUP BY UserID, GroupID
HAVING COUNT(*) >= 3; -- Find streaks of 3+ daysThe insight: If dates are consecutive (1, 2, 3, 4) and you subtract their row numbers (1, 2, 3, 4), you get the same value (0, 0, 0, 0). Non-consecutive dates produce different values, naturally grouping consecutive streaks together.
Scenario 11: Monthly Sales Report with Year-over-Year Comparison
SELECT
YEAR(OrderDate) AS SalesYear,
MONTH(OrderDate) AS SalesMonth,
SUM(Amount) AS MonthlyTotal,
LAG(SUM(Amount)) OVER (
PARTITION BY MONTH(OrderDate) ORDER BY YEAR(OrderDate)
) AS PreviousYearSame Month,
ROUND(
(SUM(Amount) - LAG(SUM(Amount)) OVER (
PARTITION BY MONTH(OrderDate) ORDER BY YEAR(OrderDate)
)) * 100.0 / LAG(SUM(Amount)) OVER (
PARTITION BY MONTH(OrderDate) ORDER BY YEAR(OrderDate)
), 2
) AS GrowthPercent
FROM Orders
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY SalesYear, SalesMonth;Key Patterns to Memorize
| Pattern | SQL Technique | When to Use |
|---|---|---|
| Nth highest/lowest | DENSE_RANK() + subquery | Ranking questions |
| Find unmatched | LEFT JOIN + WHERE NULL | Missing relationships |
| Remove duplicates | ROW_NUMBER() + DELETE | Data cleaning |
| Running totals | SUM() OVER (ORDER BY) | Cumulative calculations |
| Top N per group | RANK() + PARTITION BY | Category-wise rankings |
| Consecutive sequences | ROW_NUMBER date trick | Streak detection |
| Compare periods | LAG/LEAD window functions | Growth/change analysis |
Tips for SQL Interviews
- Always clarify edge cases — What about ties? NULLs? Empty tables?
- Start simple, then optimize — Write the correct query first, improve performance after
- Know your window functions — ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM OVER
- Practice on real databases — LeetCode SQL, HackerRank, SQLZoo
- Explain your thinking aloud — Interviewers value the thought process as much as the answer
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Scenario-Based Interview Questions.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, interview, questions, scenario
Related SQL Complete Guide Topics