SQL interviews are often overlooked in favor of data structures and algorithms. That's a mistake. SQL problems appear in almost every senior-level engineering interview—especially at companies like Amazon, Google, and Microsoft. And they're not just simple SELECT statements. Senior engineers are expected to write complex queries with joins, aggregations, window functions, and optimization considerations. This guide covers the SQL patterns and problems that separate junior engineers from seniors.

Why SQL Matters

In real engineering work, most of your time isn't spent writing algorithms—it's spent querying and manipulating data. Companies care deeply about whether you can write efficient queries, understand indexing, and think about performance. A junior engineer writes a slow query that runs for 30 minutes. A senior engineer writes a query that runs in 1 second.

SQL Interview Levels

Level 1: Basic Queries (Junior)

• Simple SELECT, WHERE, ORDER BY
• Single table queries

Level 2: Joins and Aggregations (Mid-level)

• INNER JOIN, LEFT JOIN, GROUP BY
• Subqueries
• Aggregation functions

Level 3: Window Functions and Advanced (Senior)

• OVER(), PARTITION BY, ROW_NUMBER()
• Complex business logic
• Query optimization
• Understanding execution plans

Pattern 1: SELECT with WHERE and ORDER

Problem: Find the top 3 highest-paid employees from each department.

SELECT
    department,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees
WHERE rank <= 3;

Wait, this won't work! You can't use window functions in WHERE. Fix:

WITH ranked_employees AS (
    SELECT
        department,
        employee_name,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank
    FROM employees
)
SELECT * FROM ranked_employees WHERE rank <= 3;

Key insight: CTEs (Common Table Expressions) are your friend. They make complex queries readable and let you use window functions before filtering.

Pattern 2: JOINs

Problem: Find all employees and their department names, including employees with no department assigned.

SELECT
    e.employee_id,
    e.employee_name,
    d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

Common join types:

• INNER JOIN: Rows that exist in both tables
• LEFT JOIN: All rows from left table + matching rows from right
• RIGHT JOIN: All rows from right table + matching rows from left
• FULL OUTER JOIN: All rows from both tables

When to use each:

• INNER JOIN: Both sides must have data
• LEFT JOIN: Keep all from left, match from right if possible
• FULL OUTER JOIN: Rare, but useful for reconciliation

Pattern 3: GROUP BY and HAVING

Problem: Find departments with more than 5 employees, ordered by headcount.

SELECT
    d.department_name,
    COUNT(e.employee_id) as headcount
FROM departments d
LEFT JOIN employees e ON d.department_id = e.department_id
GROUP BY d.department_name
HAVING COUNT(e.employee_id) > 5
ORDER BY headcount DESC;

Key points:

• GROUP BY aggregates rows
• HAVING filters after aggregation (WHERE filters before)
• Always aggregate the correct column (COUNT vs COUNT(DISTINCT))

Common mistake: Using aggregate in GROUP BY

-- Wrong:
SELECT COUNT(*) as count FROM employees GROUP BY COUNT(*);
 
-- Right:
SELECT COUNT(*) as count FROM employees;

Pattern 4: Subqueries and CTEs

Problem: Find employees who earn more than the average salary in their department.

Using subquery:

SELECT e.employee_name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(e2.salary)
    FROM employees e2
    WHERE e2.department_id = e.department_id
);

Using CTE (more readable):

WITH dept_avg_salary AS (
    SELECT
        department_id,
        AVG(salary) as avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT
    e.employee_name,
    e.salary
FROM employees e
JOIN dept_avg_salary d ON e.department_id = d.department_id
WHERE e.salary > d.avg_salary;

Key insight: CTEs are more readable than nested subqueries. Use them liberally.

Pattern 5: Window Functions

Window functions are where senior engineers shine. They compute aggregates over windows of rows without reducing the result set.

ROW_NUMBER(): Rank rows uniquely

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

RANK(): Allow ties

SELECT
    employee_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) as rank
FROM employees;

DENSE_RANK(): Rank without gaps

-- ROW_NUMBER:     1, 2, 3, 4
-- RANK:           1, 1, 3, 4 (gap after tie)
-- DENSE_RANK:     1, 1, 2, 3 (no gap)

PARTITION BY: Separate windows for subgroups

SELECT
    department_id,
    employee_name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees;

LAG() and LEAD(): Access previous/next rows

SELECT
    employee_name,
    hire_date,
    salary,
    LAG(salary) OVER (ORDER BY hire_date) as prev_salary
FROM employees;

Problem: Find employees who received a raise (current salary > previous salary).

WITH salary_changes AS (
    SELECT
        employee_id,
        employee_name,
        salary,
        LAG(salary) OVER (PARTITION BY employee_id ORDER BY year) as prev_salary
    FROM salary_history
)
SELECT
    employee_name,
    prev_salary,
    salary
FROM salary_changes
WHERE salary > prev_salary;

Pattern 6: Self-Join

Problem: Find employees who work in the same department and have been there for more than 2 years.

SELECT
    e1.employee_name,
    e2.employee_name,
    e1.department_id
FROM employees e1
JOIN employees e2 ON e1.department_id = e2.department_id
    AND e1.employee_id < e2.employee_id
    AND YEAR(NOW()) - YEAR(e1.hire_date) > 2
    AND YEAR(NOW()) - YEAR(e2.hire_date) > 2;

Key insight: Self-join is useful for finding relationships within the same table.

Pattern 7: UNION and UNION ALL

Problem: Get a combined list of both current and historical employee salaries.

SELECT employee_name, salary, 'current' as type
FROM employees
UNION
SELECT employee_name, salary, 'historical' as type
FROM salary_history;

Key difference:

• UNION: Removes duplicates
• UNION ALL: Keeps duplicates (faster)

Use UNION ALL unless you specifically need to remove duplicates.

Pattern 8: CASE Statements

Problem: Categorize employees by salary range.

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary < 50000 THEN 'Junior'
        WHEN salary < 100000 THEN 'Mid'
        WHEN salary < 150000 THEN 'Senior'
        ELSE 'Principal'
    END as level
FROM employees;

Nested CASE for complex logic:

SELECT
    employee_name,
    CASE
        WHEN department_id = 1 THEN
            CASE
                WHEN salary > 100000 THEN 'Senior Developer'
                ELSE 'Junior Developer'
            END
        WHEN department_id = 2 THEN 'Manager'
        ELSE 'Other'
    END as role
FROM employees;

Pattern 9: DISTINCT and COUNT(DISTINCT)

Problem: How many unique departments have employees?

SELECT COUNT(DISTINCT department_id) as unique_departments
FROM employees;

Avoid:

-- Wrong: Counts unique combinations, not unique departments
SELECT COUNT(DISTINCT department_id, employee_id) FROM employees;

Pattern 10: Date Functions

Problem: Find employees hired in the last 30 days.

SELECT *
FROM employees
WHERE hire_date >= DATE_SUB(NOW(), INTERVAL 30 DAY);

Common date functions:

• DATE_ADD(date, INTERVAL n DAY)
• DATE_SUB(date, INTERVAL n DAY)
• DATEDIFF(date1, date2)
• YEAR(), MONTH(), DAY()
• DATE_FORMAT(date, format)

Pattern 11: String Functions

Problem: Find employees whose names contain 'John'.

SELECT *
FROM employees
WHERE employee_name LIKE '%John%';

Common string functions:

• CONCAT(str1, str2, ...)
• UPPER(), LOWER()
• SUBSTRING(str, start, length)
• REPLACE(str, old, new)
• LENGTH(str)
• TRIM() (remove leading/trailing spaces)

Interview Scenario: Complex Query

Problem: For each department, find the top 3 highest-paid employees and the percentage they represent of the department's total payroll.

WITH dept_totals AS (
    SELECT
        department_id,
        SUM(salary) as total_salary
    FROM employees
    GROUP BY department_id
),
ranked_employees AS (
    SELECT
        e.department_id,
        e.employee_name,
        e.salary,
        dt.total_salary,
        ROW_NUMBER() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) as rank
    FROM employees e
    JOIN dept_totals dt ON e.department_id = dt.department_id
)
SELECT
    department_id,
    employee_name,
    salary,
    ROUND(100.0 * salary / total_salary, 2) as percentage
FROM ranked_employees
WHERE rank <= 3
ORDER BY department_id, rank;

What does this show?

• Using CTEs for clarity
• Window functions for ranking
• Join operations
• Aggregation with SUM
• Percentage calculation
• Proper rounding and formatting

Common SQL Mistakes

Mistake 1: Using non-aggregated columns in GROUP BY

-- Wrong (MySQL might allow, but it's incorrect):
SELECT e.employee_id, e.salary FROM employees e GROUP BY e.department_id;
 
-- Right:
SELECT e.department_id, COUNT(*) FROM employees e GROUP BY e.department_id;

Mistake 2: Using WHERE instead of HAVING

-- Wrong:
SELECT department_id, COUNT(*) FROM employees WHERE COUNT(*) > 5 GROUP BY department_id;
 
-- Right:
SELECT department_id, COUNT(*) FROM employees GROUP BY department_id HAVING COUNT(*) > 5;

Mistake 3: JOIN mistakes with duplicates

-- If you join on non-unique keys, you get a Cartesian product:
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- If 1 customer has 10 orders, this returns 10 rows per customer (correct in this case)

Performance Considerations

Indexing:

-- Create index on frequently searched columns:
CREATE INDEX idx_employee_dept ON employees(department_id);
CREATE INDEX idx_salary ON employees(salary);

EXPLAIN PLAN: Understand your query's performance.

EXPLAIN SELECT * FROM employees WHERE salary > 100000;

This shows you the execution plan, helping you identify bottlenecks.

Avoid costly operations:

• LIKE with leading % is slow (can't use index)
• Functions on indexed columns disable indexes
• Subqueries in SELECT (instead use JOIN or CTE)

SQL Interview Tips

1. Start simple, build up. Write basic query first, then add complexity.
2. State your approach. "I'll join these tables, aggregate here, and filter with HAVING."
3. Test on examples. Manually verify your logic on a small dataset.
4. Ask clarifying questions. "Can there be NULL values?" "Are there duplicates?"
5. Think about performance. "This uses an index, so it should be fast."
6. Explain your reasoning. "I used PARTITION BY because I need separate windows for each department."

Practicing SQL Effectively

1. Set up a local database (MySQL, PostgreSQL).
2. Create sample schema and data.
3. Solve 20-30 problems on LeetCode (Database section) or HackerRank.
4. Write queries without looking at solutions.
5. Optimize for clarity and performance.

Preparing for SQL Interviews

SQL interviews require thinking about correctness, clarity, and performance. They're often overlooked in interview prep, but mastering SQL is a significant advantage. Phantom Code supports SQL interviews and can help you practice complex queries with real-time feedback. While it's primarily designed for algorithm interviews, SQL is increasingly important for senior roles. Plans start at ₹499/month at phantomcode.co.

Final Thoughts

SQL is a superpower that many engineers underestimate. Master these patterns and you'll solve SQL interview questions confidently and efficiently. You'll also write better queries in your day job, which compounds into career-long benefits.