SQL Interview Prep

10 SQL Interview Tips That Senior Engineers Swear By (2026)

The difference between passing and failing an SQL interview isn't just knowing the syntax — it's knowing how to think, structure your answers, and avoid the traps most candidates fall into.

📅 Updated April 2026  |  ⏱ 10 min read  |  🎯 All Levels

The 10 Tips

1Always Think Out Loud Before WritingStrategy

Before touching the keyboard, spend 60 seconds clarifying: "Do I need to handle NULLs? What's the expected output for ties? Should I include/exclude rows with no matches?"

Interviewers give credit for asking the right questions. A query that handles edge cases correctly scores higher than a "perfect" query with silent bugs. Silence is the worst approach.

✅ Script for the First 60 Seconds "Let me understand the expected output first. If a customer has no orders, should they appear in the result? And should I count NULL amounts as zero or exclude them?" — This one habit alone separates senior candidates.

2Know Your NULL Traps ColdCorrectness

NULLs trip up the majority of SQL interview candidates. Remember: any comparison with NULL returns NULL, not TRUE or FALSE.

-- WRONG — will silently exclude NULLs
SELECT * FROM users WHERE discount = NULL;

-- CORRECT
SELECT * FROM users WHERE discount IS NULL;

-- WRONG — COUNT(*) counts NULLs, COUNT(col) does NOT
SELECT COUNT(discount) FROM orders;   -- excludes NULL discounts
SELECT COUNT(*) FROM orders;          -- counts everything

-- NULL-safe aggregation pattern
SELECT COALESCE(SUM(discount), 0) AS total_discount FROM orders;
-- Returns 0 even if all discounts are NULL

❌ Common TrapAVG(col) automatically ignores NULLs. If 3 of 10 rows have NULL revenue, AVG(revenue) divides the sum by 7, not 10. If you want a true average treating NULLs as zero: AVG(COALESCE(revenue, 0)).

3Understand WHERE vs HAVING — Never Confuse ThemCorrectness

WHERE filters individual rows before aggregation. HAVING filters groups after aggregation.

-- Find departments with average salary > 80k (only active employees)
SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'           -- WHERE: filter rows first
GROUP BY department
HAVING AVG(salary) > 80000;       -- HAVING: filter groups after aggregation

-- Performance tip: always push filters to WHERE when possible
-- HAVING(some_column = value) is slower than WHERE(some_column = value)
-- because HAVING runs after grouping and aggregating all rows

4Know the Execution Order of SQL ClausesPerformance

SQL doesn't execute top-to-bottom. The actual execution order is:

1. FROM + JOINs   — identify and join tables
2. WHERE          — filter individual rows
3. GROUP BY       — group remaining rows
4. HAVING         — filter groups
5. SELECT         — compute output columns
6. DISTINCT       — remove duplicates
7. ORDER BY       — sort the result
8. LIMIT/OFFSET   — return subset

This is why you cannot reference a SELECT alias in a WHERE clause — WHERE runs before SELECT computes the alias.

❌ Common TrapSELECT salary * 1.1 AS adjusted, ... WHERE adjusted > 50000 — This FAILS. adjusted doesn't exist yet when WHERE runs. Solution: use a subquery or CTE, or repeat the expression: WHERE salary * 1.1 > 50000.

5Use CTEs to Make Complex Queries ReadableStyle

Nothing impresses an interviewer more than clean, readable SQL. CTEs (Common Table Expressions) break complex logic into named, readable steps — and they also signal seniority.

-- Messy nested subquery approach (junior style)
SELECT * FROM (
    SELECT customer_id, SUM(amount) AS total
    FROM (SELECT * FROM orders WHERE status = 'completed') o
    GROUP BY customer_id
) totals WHERE total > 1000;

-- CTE approach (senior style — clearly shows intent)
WITH completed_orders AS (
    SELECT customer_id, amount
    FROM orders
    WHERE status = 'completed'
),
customer_totals AS (
    SELECT customer_id, SUM(amount) AS total
    FROM completed_orders
    GROUP BY customer_id
)
SELECT customer_id, total
FROM customer_totals
WHERE total > 1000;

6Know When to Use Window Functions vs GROUP BYEfficiency

The golden rule: use GROUP BY when you want to collapse rows into one per group. Use Window Functions when you want to add aggregated values to each row without collapsing the result set.

-- "Show each employee and how their salary compares to their department average"
-- GROUP BY CANNOT do this cleanly — it would lose employee details

-- Window function approach:
SELECT
    name, department, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department) AS diff_from_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

🎯 Interview Signal If a question asks "for each row, show X compared to group average/rank" — that's a window function. Any time the word "per" or "within each" appears alongside keeping all original rows → Window Function.

7Always Mention Index ImplicationsPerformance

For any non-trivial query, add: "In production, I'd ensure there's an index on the join keys and filter columns." This one sentence signals senior-level thinking.

-- This query will be slow on large tables without indexes
SELECT o.*, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id   -- index on customer_id and c.id
WHERE o.created_at > '2026-01-01'          -- index on created_at
AND o.status = 'pending';                  -- consider composite index

-- In interviews, say:
-- "I'd add a composite index on (status, created_at) for this filter
--  since both columns appear in the WHERE clause together frequently."

8The "Find Second Highest" PatternClassic Question

A perennial interview question — know 3 ways to solve it.

-- Method 1: Subquery (simple, widely supported)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 2: LIMIT/OFFSET (clean, PostgreSQL/MySQL)
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- Method 3: Window Function (best for Nth highest in general)
WITH ranked AS (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
)
SELECT salary FROM ranked WHERE rnk = 2;
-- Change rnk = 2 to any N for the Nth highest

🎯 Pro Tip Use Method 3 in interviews — it shows you know window functions and is easily generalizable to "Nth highest", which the interviewer often follows up with.

9Know the JOIN Types and When Rows Are LostCorrectness

-- INNER JOIN — only rows matching in BOTH tables
-- LEFT JOIN  — all rows from left, NULLs for unmatched right
-- RIGHT JOIN — all rows from right, NULLs for unmatched left
-- FULL OUTER — all rows from both, NULLs on non-matching sides
-- CROSS JOIN — cartesian product (every combination)

-- Interviewer trap: "Find customers with NO orders"
-- WRONG — this returns customers WITH orders:
SELECT c.* FROM customers c JOIN orders o ON c.id = o.customer_id;

-- CORRECT — LEFT JOIN + filter for NULL
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

10Practice Explaining Your Query After Writing ItCommunication

After writing any query in an interview, do this: "Let me walk through what this does..." and narrate each clause in plain English.

This shows you understand your own code (not just copied a pattern), catches any bugs before the interviewer does, and demonstrates the communication skills every senior role requires.

✅ The 30-Second Walk-Through Template "First, I join X with Y on the customer ID to connect orders with customer info. Then I filter to only completed orders in the WHERE clause. Then I group by department and use HAVING to keep only groups with more than 100 orders. Finally I order by total revenue descending."

📊 Test Your SQL with 100 Interview Questions

Practice our free interactive SQL quiz — 100 real interview questions with instant colour-coded feedback and full explanations.

Take the SQL Quiz → Get the PDF Bundle