# SQL Interview Questions: Joins, Window Functions, and Query Optimization (2025)

Source: https://www.techinterview.org/post/3233460468/sql-interview-questions/
Updated: 2026-04-16 · techinterview.org

SQL is tested in almost every data engineering, analytics engineering, backend, and data science interview. Questions range from writing a join to explaining a query execution plan. This guide covers the patterns that appear most frequently.

## JOIN Types

| JOIN Type | Returns | Use When |
| --- | --- | --- |
| INNER JOIN | Rows matching in both tables | You only want matching records |
| LEFT JOIN | All left rows, matching right (NULL if no match) | Keep all left records even without a match |
| RIGHT JOIN | All right rows, matching left (NULL if no match) | Rare — rewrite as LEFT JOIN instead |
| FULL OUTER JOIN | All rows from both, NULLs where no match | Find rows in either table with no correspondence |
| CROSS JOIN | Cartesian product (M x N rows) | Generate all combinations |
| SELF JOIN | Table joined to itself | Hierarchies, finding pairs |


```
-- Find all employees and their manager (NULL if no manager)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find employees without a manager
SELECT e.name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
WHERE m.id IS NULL;
```


## Aggregation and GROUP BY


```
-- Top 3 departments by average salary
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
ORDER BY avg_sal DESC
LIMIT 3;

-- Departments where average salary > 100,000
SELECT dept, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING AVG(salary) > 100000;  -- HAVING filters after GROUP BY; WHERE filters before
```


**WHERE vs HAVING**: WHERE filters individual rows before grouping; HAVING filters groups after aggregation. You cannot use aggregate functions in WHERE.

## Window Functions

Window functions compute a value across rows related to the current row without collapsing them (unlike GROUP BY). Available in PostgreSQL, MySQL 8+, SQL Server, BigQuery, Snowflake.


```
-- Rank employees by salary within each department
SELECT
    name,
    dept,
    salary,
    RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
    DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dense_rnk,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS row_num
FROM employees;

-- Running total of sales by date
SELECT
    sale_date,
    amount,
    SUM(amount) OVER (ORDER BY sale_date
                      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM sales;

-- 7-day moving average
SELECT
    sale_date,
    amount,
    AVG(amount) OVER (ORDER BY sale_date
                      ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM sales;

-- Difference between current and previous row (day-over-day change)
SELECT
    sale_date,
    amount,
    LAG(amount, 1) OVER (ORDER BY sale_date) AS prev_day,
    amount - LAG(amount, 1) OVER (ORDER BY sale_date) AS day_change
FROM sales;
```


### RANK vs DENSE_RANK vs ROW_NUMBER

| Function | Tie behavior | Example (scores: 90,90,80) |
| --- | --- | --- |
| RANK() | Same rank, next rank skips | 1, 1, 3 |
| DENSE_RANK() | Same rank, next rank does not skip | 1, 1, 2 |
| ROW_NUMBER() | Unique, arbitrary tie-break | 1, 2, 3 |

## Common Interview Problems

### Nth Highest Salary


```
-- 2nd highest salary (no duplicates)
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Nth highest using window function
SELECT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 2;
```


### Consecutive Records / Gaps and Islands


```
-- Find users who logged in 3+ consecutive days
SELECT user_id, login_date,
       login_date - (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) || ' days')::interval AS grp
FROM user_logins
-- Rows in the same "island" have the same grp value
```


### Duplicate Detection and Deduplication


```
-- Find duplicate emails
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Delete duplicates, keeping the lowest id
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id) FROM users GROUP BY email
);

-- Or with window function (safer — see what you will delete first)
WITH dedup AS (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM dedup WHERE rn > 1);
```


### Self-Join: Find Pairs


```
-- Find all pairs of employees in the same department
SELECT a.name, b.name, a.dept
FROM employees a
JOIN employees b ON a.dept = b.dept AND a.id < b.id;  -- a.id < b.id avoids (A,B) and (B,A)
```


## Indexes and Query Optimization

### When Does an Index Help?

- Equality lookups: `WHERE user_id = 123`

- Range scans: `WHERE created_at BETWEEN ... AND ...`

- ORDER BY matching the index order (avoids a sort)

- Covering index: index includes all columns needed by the query (no table heap fetch)

### When Does an Index NOT Help?

- Low-cardinality columns (status = "active" when 90% are active — full scan is cheaper)

- Functions on indexed columns: `WHERE LOWER(email) = ...` — index on `email` not used. Fix: functional index or store pre-lowercased value

- Leading wildcard: `WHERE name LIKE "%smith%"` — index skipped. Fix: full-text index or search engine

- Implicit type cast: `WHERE user_id = "123"` (user_id is INT) — cast prevents index use

### Reading EXPLAIN / EXPLAIN ANALYZE


```
EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL "30 days"
GROUP BY u.id, u.name;

-- Key things to look for:
-- Seq Scan vs Index Scan (index = good for selective queries)
-- Hash Join vs Nested Loop (hash join for large sets; nested loop for small right side)
-- rows= estimate vs actual= actual (large discrepancy = stale statistics, run ANALYZE)
-- cost= (startup cost, total cost in planner units)
-- actual time= (milliseconds)
```


## CTEs vs Subqueries vs Temp Tables

| Tool | Materialized? | Reusable? | Recursive? | Use When |
| --- | --- | --- | --- | --- |
| Subquery | Inline | No | No | Simple one-time filter or scalar value |
| CTE (WITH) | Sometimes | Within query | Yes | Readability, multi-step logic, recursive trees |
| Temp Table | Yes (disk) | Within session | No | Large intermediate result, reused multiple times |


```
-- Recursive CTE: org chart hierarchy
WITH RECURSIVE org_tree AS (
    -- Base case: CEO (no manager)
    SELECT id, name, manager_id, 0 AS depth
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: direct reports
    SELECT e.id, e.name, e.manager_id, t.depth + 1
    FROM employees e
    JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree ORDER BY depth, name;
```


## Transactions and Isolation Levels


```
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both updates succeed or neither does (atomicity)

-- Isolation levels (in order of strictness):
-- READ UNCOMMITTED: can read uncommitted changes (dirty reads)
-- READ COMMITTED (default in most DBs): only read committed data
-- REPEATABLE READ: same row reads return same value within transaction
-- SERIALIZABLE: transactions appear to run serially (strongest, slowest)
```


## Quick Reference: Must-Know SQL Functions

- `COALESCE(a, b, c)` — returns first non-NULL value

- `NULLIF(a, b)` — returns NULL if a = b, else a (use to avoid divide-by-zero)

- `CASE WHEN ... THEN ... ELSE ... END` — conditional expression

- `DATE_TRUNC("month", ts)` — truncate timestamp to month (PostgreSQL)

- `EXTRACT(DOW FROM ts)` — day of week from timestamp

- `STRING_AGG(col, ", ")` — concatenate strings within a group

- `ARRAY_AGG(col)` — aggregate values into an array (PostgreSQL)

- `UNNEST(array_col)` — expand array into rows (PostgreSQL)

## Frequently Asked Questions

### What is the difference between WHERE and HAVING in SQL?

WHERE filters individual rows before any grouping occurs. HAVING filters groups after GROUP BY has been applied. You cannot use aggregate functions (COUNT, SUM, AVG, MAX, MIN) in a WHERE clause — that is a syntax error. Use HAVING when your filter condition involves an aggregate: "departments where average salary > 100,000" requires HAVING AVG(salary) > 100000. WHERE is evaluated earlier in the query execution plan and is more efficient — push filters into WHERE whenever possible.

### What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

All three are window functions that assign a number to each row within a partition ordered by a column. ROW_NUMBER assigns a unique sequential number — ties get different numbers arbitrarily (1,2,3). RANK gives the same rank to ties but skips the next rank: two rows tied for 1st are both ranked 1, and the next row is ranked 3 (not 2). DENSE_RANK also gives the same rank to ties but does not skip: two rows tied for 1st are both 1, the next is 2. Use DENSE_RANK when you want "Nth highest" queries; use ROW_NUMBER for deduplication (keep the row with row_number = 1 per group).

### How do indexes affect SQL query performance?

An index allows the database to find rows without scanning the entire table. B-tree indexes (the default) support equality lookups, range scans, and ORDER BY matching the index order. An index helps when the query is selective (filters out most rows). An index does NOT help when: using functions on indexed columns (LOWER(email) = ...), using leading wildcards (LIKE "%smith%"), the column has low cardinality and most rows match, or the planner decides a sequential scan is cheaper (common when reading >10-20% of the table). A covering index (includes all columns the query needs) eliminates the extra heap lookup and can give dramatic speedups.