Common SQL Interview Query Patterns
💡 Concept Name
SQL Interview Query Patterns include frequently tested query structures such as filtering, aggregation, joining, grouping, window functions, and subqueries that evaluate SQL logic and problem-solving skill.
📘 Quick Intro
Most SQL interviews focus on how well you can write and optimize queries to extract insights from data. These patterns include selecting top N records, joining related tables, aggregating results, identifying duplicates, and ranking or filtering based on conditions.
🧠 Analogy / Short Story
Imagine SQL queries as kitchen recipes. Joins are your ingredients, filters are the spices, GROUP BY is the plating arrangement, and subqueries are your prep work. Interviewers want to see if you can follow the recipe, customize it, and still deliver a tasty dish (accurate query) under time pressure.
🔧 Technical Explanation
- 🔗 Join patterns – INNER JOIN, LEFT JOIN, SELF JOIN
- 🧮 Aggregation – SUM, COUNT, AVG with GROUP BY and HAVING
- 🔍 Filtering – WHERE clauses and conditional logic
- 🏆 Ranking – ROW_NUMBER(), RANK(), DENSE_RANK()
- 🔁 Subqueries – correlated and non-correlated
- 🚫 NULL handling – IS NULL, COALESCE, ISNULL
🎯 Purpose & Use Case
- ✅ Solve real business problems in SQL interviews.
- ✅ Demonstrate SQL fluency using industry-standard patterns.
- ✅ Handle edge cases like NULLs, ties, and empty groups.
- ✅ Apply filtering and aggregation in complex datasets.
💻 Real Code Example
-- 1. Top 3 salaries per department
SELECT *
FROM (
SELECT Name, Salary, Department,
RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) as rnk
FROM Employees
) ranked
WHERE rnk <= 3;
-- 2. Count orders per customer
SELECT CustomerID, COUNT(*) as OrderCount
FROM Orders
GROUP BY CustomerID
HAVING COUNT(*) > 5;
-- 3. Find duplicates
SELECT Email, COUNT(*)
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;
❓ Interview Q&A
Q1: Why are JOINs important in SQL interviews?
A: They show your ability to combine related data from multiple tables effectively.
Q2: What does the HAVING clause do?
A: It filters groups after aggregation, unlike WHERE which filters rows before grouping.
Q3: How can you get the second highest salary?
A: Use RANK() or a subquery with MAX() and < logic.
Q4: What is a correlated subquery?
A: A subquery that refers to columns from the outer query and runs for each row.
Q5: How do you find duplicates in a column?
A: Use GROUP BY and HAVING COUNT(*) > 1.
Q6: What is the use of window functions?
A: To perform calculations across rows while retaining row-level data (like ranking, percentiles).
Q7: How can NULLs affect query logic?
A: NULLs can break logic in comparisons and aggregations unless handled explicitly.
Q8: Difference between INNER JOIN and LEFT JOIN?
A: INNER JOIN returns matches only; LEFT JOIN includes unmatched rows from the left table with NULLs.
Q9: What’s a use case for a CTE?
A: Simplifies nested queries and improves readability with named temporary results.
Q10: How to limit results per group in SQL?
A: Use window functions like ROW_NUMBER() or RANK() with filtering in outer query.
📝 MCQs
Q1. Which clause filters groups in SQL?
- WHERE
- HAVING
- GROUP BY
- FILTER
Q2. Which window function ranks without gaps?
- RANK()
- ROW_NUMBER()
- NTILE()
- DENSE_RANK()
Q3. What does a LEFT JOIN return?
- Only matches
- Left unmatched
- All right rows
- All left rows + matching right rows
Q4. Which clause creates row groups?
- HAVING
- PARTITION BY
- GROUP BY
- ORDER BY
Q5. What is a CTE in SQL?
- Common Table Expression
- Conditional Test Expression
- Column Type Element
- Custom Table Element
Q6. What function gets unique values per row?
- RANK()
- ROW_NUMBER()
- SUM()
- AVG()
Q7. Which clause sorts results?
- SORT
- ORDER BY
- GROUP BY
- SELECT
Q8. Which query finds duplicates?
- DISTINCT
- GROUP BY + HAVING COUNT(*) > 1
- LEFT JOIN
- IS NULL
Q9. What does a correlated subquery use?
- Temp tables
- WHERE clause
- Outer query columns
- Indexes
Q10. How to get top N rows per group?
- GROUP BY only
- JOIN and LIMIT
- Subqueries
- Window functions + filter
💡 Bonus Insight
In interviews, clarity matters as much as correctness. Break large queries into CTEs, always alias your columns, and prioritize readability. Practice under timed conditions and always validate with sample data before finalizing your solution.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!