SQL Interview Questions 2026: Complex Queries, Window Functions, Optimization

Why SQL Matters

Every backend engineer needs strong SQL skills. Interviewers test your ability to write efficient queries and design schemas. SQL is the lingua franca of data — whether you are building APIs, analytics pipelines, or data warehouses, you will write SQL daily. Mastering it means faster development, better performance, and more insightful data analysis.

Basic Questions

Q1: INNER JOIN vs LEFT JOIN

```sql
-- INNER JOIN: Only matching rows from both tables
SELECT e.name, d.department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- LEFT JOIN: All rows from left table, NULLs where no match
SELECT e.name, d.department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN: All rows from right table, NULLs where no match
SELECT e.name, d.department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

-- FULL OUTER JOIN: All rows from both tables
SELECT e.name, d.department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;
```

Key insight: LEFT JOIN is the most commonly used in practice. It lets you find rows in the left table with no matching rows in the right table by adding WHERE d.id IS NULL.

Q2: WHERE vs HAVING

```sql
-- WHERE: Filters before grouping (cannot use aggregate functions)
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department;

-- HAVING: Filters after grouping (can use aggregate functions)
SELECT department, COUNT()
FROM employees
GROUP BY department
HAVING COUNT() > 5;

-- Combining both: WHERE filters rows first, then HAVING filters groups
SELECT department, AVG(salary) as avg_salary
FROM employees
WHERE hire_date > '2020-01-01'
GROUP BY department
HAVING AVG(salary) > 80000;
```

Q3: DELETE vs TRUNCATE vs DROP

DELETE removes rows one by one, logging each deletion. TRUNCATE deallocates data pages in bulk, making it much faster for large tables. DROP removes the entire table definition and data.

Intermediate Questions

Q4: Window Functions Deep Dive

Window functions perform calculations across a set of rows related to the current row without collapsing them into a single output row like GROUP BY does.

```sql
-- ROW_NUMBER: Sequential numbering, ties get different numbers
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num
FROM employees;

-- RANK: Same rank for ties, gaps after ties
SELECT name, department, salary,
RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_num
FROM employees;

-- DENSE_RANK: Same rank for ties, no gaps
SELECT name, department, salary,
DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank_num
FROM employees;

-- NTILE: Divides rows into N roughly equal groups
SELECT name, salary,
NTILE(4) OVER (ORDER BY salary DESC) as salary_quartile
FROM employees;

-- LAG and LEAD: Access previous and next row values
SELECT date, revenue,
LAG(revenue, 1) OVER (ORDER BY date) as prev_day_revenue,
LEAD(revenue, 1) OVER (ORDER BY date) as next_day_revenue
FROM daily_sales;

-- First and Last Value in a partition
SELECT name, department, salary,
FIRST_VALUE(name) OVER (PARTITION BY department ORDER BY salary DESC) as top_earner,
LAST_VALUE(name) OVER (
PARTITION BY department ORDER BY salary DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) as lowest_earner
FROM employees;

-- Running total and moving average
SELECT date, amount,
SUM(amount) OVER (ORDER BY date) as running_total,
AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7day
FROM transactions;

-- Percentage of total per group
SELECT department, name, salary,
ROUND(salary * 100.0 / SUM(salary) OVER (PARTITION BY department), 2) as pct_of_dept
FROM employees;
```

Common mistake: Forgetting that window functions execute AFTER WHERE and GROUP BY but BEFORE SELECT. This means you cannot reference a window function alias in the WHERE clause.

Q5: Self Joins

Self joins join a table to itself. They are essential for hierarchical data, finding pairs, and comparing rows.

```sql
-- Find employees and their managers
SELECT e.name as employee, m.name as manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Find employees in the same department (pairs without duplicates)
SELECT a.name, b.name, a.department
FROM employees a
INNER JOIN employees b ON a.department = b.department AND a.id < b.id;

-- Find employees who earn more than their manager
SELECT e.name, e.salary as emp_salary, m.name, m.salary as mgr_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- Find departments with more than 3 employees using self join
SELECT a.department, COUNT(DISTINCT a.id)
FROM employees a
INNER JOIN employees b ON a.department = b.department
GROUP BY a.department
HAVING COUNT(DISTINCT b.id) > 3;
```

Q6: Correlated Subqueries

A correlated subquery references a column from the outer query. It executes once per row of the outer query, which can be slow on large datasets.

```sql
-- Employees earning more than their department average
SELECT name, salary, department
FROM employees e1
WHERE salary > (
SELECT AVG(salary)
FROM employees e2
WHERE e2.department = e1.department
);

-- Find the latest order for each customer
SELECT o1.*
FROM orders o1
WHERE o1.created_at = (
SELECT MAX(created_at)
FROM orders o2
WHERE o2.customer_id = o1.customer_id
);

-- EXISTS is often faster than IN for correlated subqueries
SELECT name
FROM customers c
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000
);

-- NOT EXISTS for finding customers with no orders
SELECT name
FROM customers c
WHERE NOT EXISTS (
SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
```

Performance tip: Replace IN (SELECT ...) with EXISTS or rewrite as a JOIN when possible. The optimizer handles EXISTS more efficiently because it can short-circuit on the first match.

Q7: Common Table Expressions (CTEs)

CTEs make complex queries readable and enable recursive queries for hierarchical data.

```sql
-- Basic CTE for readability
WITH department_stats AS (
SELECT department, AVG(salary) as avg_salary, COUNT(*) as headcount
FROM employees
GROUP BY department
)
SELECT e.name, e.salary, ds.avg_salary,
e.salary - ds.avg_salary as diff_from_avg
FROM employees e
INNER JOIN department_stats ds ON e.department = ds.department;

-- Multiple CTEs chained together
WITH monthly_revenue AS (
SELECT DATE_TRUNC('month', created_at) as month, SUM(total) as revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at
),
monthly_costs AS (
SELECT DATE_TRUNC('month', created_at) as month, SUM(cost) as costs
FROM expenses
GROUP BY DATE_TRUNC('month', created_at)
)
SELECT r.month, r.revenue, c.costs, r.revenue - c.costs as profit
FROM monthly_revenue r
INNER JOIN monthly_costs c ON r.month = c.month
ORDER BY r.month;

-- Recursive CTE for organizational hierarchy
WITH RECURSIVE org_chart AS (
-- Anchor: top-level managers
SELECT id, name, manager_id, 1 as level
FROM employees
WHERE manager_id IS NULL
UNION ALL
-- Recursive: direct reports
SELECT e.id, e.name, e.manager_id, oc.level + 1
FROM employees e
INNER JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level, name;

-- Recursive CTE for generating date series
WITH RECURSIVE date_series AS (
SELECT DATE '2026-01-01' as dt
UNION ALL
SELECT dt + INTERVAL '1 day' FROM date_series WHERE dt < DATE '2026-12-31'
)
SELECT dt FROM date_series;
```

Interview tip: Recursive CTEs are a favorite topic. Practice writing one that traverses a tree structure — it demonstrates understanding of both CTEs and recursive logic.

Advanced Questions

Q8: Index Optimization

```sql
-- Create composite index for common query pattern
CREATE INDEX idx_emp_dept_salary ON employees(department, salary);

-- Query uses index (department first, then salary)
SELECT * FROM employees WHERE department = 'Engineering' AND salary > 100000;

-- Partial index for specific conditions
CREATE INDEX idx_active_users ON users(email) WHERE is_active = true;

-- Covering index avoids table lookups entirely
CREATE INDEX idx_covering ON orders(customer_id, total) INCLUDE (created_at);
SELECT total, created_at FROM orders WHERE customer_id = 42;

-- Index for text search
CREATE INDEX idx_name_gin ON employees USING gin(name gin_trgm_ops);
```

Index Rules:

• Leftmost prefix rule: composite index on (A, B, C) helps queries filtering by A, A+B, or A+B+C but not B+C alone
• Covering indexes avoid table lookups — all needed columns are in the index
• Avoid indexing low-cardinality columns (like boolean flags) unless combined with other columns
• Partial indexes save space when you only query a subset of rows

Q9: Query Optimization

```sql
-- Slow: Correlated subquery recalculates for every row
SELECT * FROM orders o
WHERE total > (SELECT AVG(total) FROM orders WHERE customer_id = o.customer_id);

-- Fast: Window function calculates once
SELECT * FROM (
SELECT *, AVG(total) OVER (PARTITION BY customer_id) as avg_total
FROM orders
) sub
WHERE total > avg_total;

-- Slow: NOT IN with NULLs returns no results
SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

-- Fast: NOT EXISTS handles NULLs correctly
SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Slow: SELECT * pulls unnecessary data
SELECT * FROM orders WHERE customer_id = 42;

-- Fast: Select only what you need
SELECT id, total, created_at FROM orders WHERE customer_id = 42;

-- Use EXPLAIN ANALYZE to see actual execution plans
EXPLAIN ANALYZE
SELECT e.name, d.department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 80000;
```

Optimization checklist:

1. Check for missing indexes on JOIN and WHERE columns
2. Avoid SELECT * — fetch only needed columns
3. Replace correlated subqueries with window functions or JOINs
4. Use LIMIT when you only need top N rows
5. Check for implicit type casts that prevent index usage

Q10: Transaction Isolation Levels

Isolation levels control how transactions interact with each other. The trade-off is between consistency and concurrency.

```sql
-- Read Uncommitted: Can see uncommitted changes from other transactions
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT * FROM accounts WHERE id = 1; -- Might see partial writes

-- Read Committed: Only sees committed data (default in PostgreSQL)
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
-- Another transaction updates balance to 1500 and commits
SELECT balance FROM accounts WHERE id = 1; -- Returns 1500 (changed!)
COMMIT;

-- Repeatable Read: Consistent snapshot within a transaction
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT balance FROM accounts WHERE id = 1; -- Returns 1000
-- Another transaction updates and commits
SELECT balance FROM accounts WHERE id = 1; -- Still returns 1000
COMMIT;

-- Serializable: Fully isolated, serial execution
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
-- Reads and writes are fully serialized
-- Other transactions blocked until this commits
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;
```

Real-world example: The classic transfer problem. Two concurrent transfers from the same account could both read balance=1000, subtract 800, and write 200 — losing 800. Serializable isolation prevents this by serializing the transactions.

Q11: Schema Design

```sql
-- Normalized design (3NF) — minimizes redundancy
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(100) UNIQUE
);

CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
total DECIMAL(10,2),
created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(id),
product_id INT REFERENCES products(id),
quantity INT,
unit_price DECIMAL(10,2)
);

-- Denormalized for read performance (analytics, dashboards)
CREATE TABLE order_summary (
user_id INT,
user_name VARCHAR(100),
total_orders INT,
total_spent DECIMAL(10,2),
last_order_date TIMESTAMP
);

-- Materialized view for expensive aggregations
CREATE MATERIALIZED VIEW monthly_sales AS
SELECT DATE_TRUNC('month', created_at) as month,
SUM(total) as revenue,
COUNT(*) as order_count
FROM orders
GROUP BY DATE_TRUNC('month', created_at);

-- Refresh when data changes
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales;
```

Design principles:

• Normalize to 3NF for write-heavy workloads to avoid update anomalies
• Denormalize for read-heavy analytics where JOINs are expensive
• Use materialized views for expensive aggregations that change infrequently
• Always define foreign keys and constraints — they enforce data integrity and help the optimizer

Practice Problems with Solutions

Problem 1: Second Highest Salary

```sql
-- Solution A: LIMIT/OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Solution B: Window function (handles ties correctly)
SELECT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rn
FROM employees
) sub
WHERE rn = 2;

-- Solution C: Subquery approach (always returns a row)
SELECT MAX(salary) as second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
```

Problem 2: Department Top 3 Earners

```sql
SELECT name, department, salary FROM (
SELECT name, department, salary,
ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rn
FROM employees
) ranked
WHERE rn <= 3
ORDER BY department, salary DESC;
```

Problem 3: Consecutive Days with Activity

```sql
WITH daily_status AS (
SELECT user_id, activity_date,
activity_date - ROW_NUMBER() OVER (
PARTITION BY user_id ORDER BY activity_date
) as grp
FROM user_activity
)
SELECT user_id, MIN(activity_date) as start_date, MAX(activity_date) as end_date,
COUNT() as consecutive_days
FROM daily_status
GROUP BY user_id, grp
HAVING COUNT() >= 3
ORDER BY user_id, start_date;
```

Problem 4: Month-over-Month Growth Rate

```sql
WITH monthly AS (
SELECT DATE_TRUNC('month', created_at) as month, SUM(total) as revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
)
SELECT month, revenue,
LAG(revenue) OVER (ORDER BY month) as prev_month,
ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0 /
NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 2) as growth_pct
FROM monthly
ORDER BY month;
```

Problem 5: Find Duplicate Records

```sql
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
);
```

Common SQL Anti-Patterns and How to Fix Them

Anti-Pattern 1: Using SELECT *

```sql
-- Bad: Fetches all columns, breaks on schema changes
SELECT * FROM orders WHERE customer_id = 42;

-- Good: Explicit column list, future-proof
SELECT id, total, status, created_at FROM orders WHERE customer_id = 42;
```

Why it matters: SELECT * transfers more data over the network, prevents covering index usage, and breaks application code when columns are added or reordered.

Anti-Pattern 2: N+1 Queries

```sql
-- Bad: One query for customers, then one query per customer
SELECT * FROM customers WHERE id IN (1,2,3);
-- Then for each: SELECT * FROM orders WHERE customer_id = ?;

-- Good: Single JOIN to get everything
SELECT c.name, o.id, o.total, o.created_at
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.id IN (1,2,3);
```

Anti-Pattern 3: Implicit Type Conversion

```sql
-- Bad: Comparing string to integer, prevents index usage
SELECT * FROM users WHERE phone = 5551234;

-- Good: Match types correctly
SELECT * FROM users WHERE phone = '5551234';
```

Anti-Pattern 4: Function on Indexed Column

```sql
-- Bad: Function call prevents index usage
SELECT * FROM orders WHERE YEAR(created_at) = 2026;

-- Good: Range scan uses index
SELECT * FROM orders
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01';

-- Alternative: Functional index
CREATE INDEX idx_orders_year ON orders(YEAR(created_at));
```

Anti-Pattern 5: Using OR Instead of IN

```sql
-- Bad: OR can prevent index optimization
SELECT * FROM products WHERE category = 'Books' OR category = 'Electronics';

-- Good: IN is often optimized better
SELECT * FROM products WHERE category IN ('Books', 'Electronics');
```

Anti-Pattern 6: Not Using EXISTS for Existence Checks

```sql
-- Bad: Counts all matching rows unnecessarily
SELECT COUNT(*) FROM orders WHERE customer_id = 42;
-- Application checks: if count > 0 ...

-- Good: Stops at first match
SELECT EXISTS (SELECT 1 FROM orders WHERE customer_id = 42);
```

Resources

• SQL Roadmap - Complete SQL learning path
• SQL Topics - Deep dives
• System Design - Database design
• Java Interview Questions - Backend language prep
• DSA Cheat Sheet - Quick reference