Top SQL Interview Questions and Answers (2026)

-- WHERE: filter individual rows
SELECT department, COUNT(*) as emp_count
FROM employees
WHERE salary > 50000          -- filter rows first
GROUP BY department;
 
-- HAVING: filter after grouping
SELECT department, COUNT(*) as emp_count
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;           -- filter groups
 
-- Using both
SELECT department, AVG(salary) as avg_sal
FROM employees
WHERE hire_date > '2020-01-01' -- exclude old hires
GROUP BY department
HAVING AVG(salary) > 70000;    -- only high-paying depts

-- Setup tables
-- employees: id, name, department_id, salary
-- departments: id, name, manager_id
 
-- INNER JOIN — only matching rows from both tables
SELECT e.name, d.name as dept
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
 
-- LEFT JOIN — all rows from left + matching from right (NULL if no match)
SELECT e.name, d.name as

-- UNION — combines and removes duplicates (slower, sorts)
SELECT name FROM employees_us
UNION
SELECT name FROM employees_eu;
 
-- UNION ALL — combines and keeps duplicates (faster)
SELECT name FROM employees_us
UNION ALL
SELECT name FROM employees_eu;
 
-- Performance: prefer UNION ALL when you know there are no duplicates
-- or when you explicitly want duplicates
 
-- Requirements: same number of columns, compatible data types

-- Find duplicate emails
SELECT email, COUNT(*) as cnt
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
 
-- Find all rows that are duplicates
SELECT *
FROM users
WHERE email IN (
  SELECT email
  FROM users
  GROUP BY email
  HAVING COUNT

-- Syntax: function() OVER (PARTITION BY ... ORDER BY ... ROWS/RANGE ...)
 
-- ROW_NUMBER — unique sequential number per partition
SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as rank_in_dept
FROM employees;
 
-- RANK vs DENSE_RANK
-- RANK: 1, 2, 2, 4  (skips after tie)
-- DENSE_RANK: 1, 2, 2, 3 (no skip)
SELECT
  name,
  salary,
  RANK()       OVER (ORDER BY

-- Basic CTE
WITH high_earners AS (
  SELECT id, name, salary, department_id
  FROM employees
  WHERE salary > 100000
)
SELECT h.name, d.name as department
FROM high_earners h
JOIN departments d ON h.department_id = d.id;
 
-- Multiple CTEs

-- Solution 1: subquery (works everywhere)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET N-1;  -- N=2 for 2nd highest
 
-- Solution 2: DENSE_RANK window function (best)
SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk
  FROM employees
) ranked
WHERE rnk 

-- B-Tree Index (default) — good for equality and range queries
CREATE INDEX idx_employees_email ON employees(email);
CREATE INDEX idx_employees_dept_salary ON employees(department_id, salary); -- composite
 
-- When to index:
-- ✓ Columns in WHERE clauses
-- ✓ JOIN columns (foreign keys)
-- ✓ ORDER BY / GROUP BY columns
-- ✓ High cardinality columns (many unique values)
 
-- When NOT to index:
-- ✗ Small tables (full scan is faster)
-- ✗ Columns rarely used in queries
-- ✗ Tables with heavy write loads (indexes slow writes)
-- ✗ Low cardinality columns (e.g., boolean flags)
 
-- Partial index — only index a subset of rows
CREATE INDEX idx_active_users ON users(email) WHERE

-- Table: Employee(id, name, salary, managerId)
-- Classic interview question at Facebook, Google, etc.
 
-- Solution using self-join
SELECT e.name as employee
FROM employee e
JOIN employee m ON e.manager_id = m.id
WHERE e.salary > m.salary;
 
-- Solution using correlated subquery
SELECT name
FROM employee e
WHERE salary 

-- Table: logins(user_id, login_date)
-- Find users with at least 3 consecutive login days
 
WITH consecutive AS (
  SELECT
    user_id,
    login_date,
    login_date - INTERVAL (ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) - 1) DAY as grp
  FROM (SELECT DISTINCT user_id, login_date FROM logins) t
),
streaks AS (

-- 1. Use EXPLAIN ANALYZE to understand query plan
EXPLAIN ANALYZE SELECT * FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at > '2024-01-01';
 
-- 2. Avoid SELECT * — specify columns
-- Bad:
SELECT * FROM large_table;
-- Good:
SELECT id, name, email FROM users;
 
-- 3. Use indexes appropriately

-- ACID: Atomicity, Consistency, Isolation, Durability
 
-- Transaction example
BEGIN;
 
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- debit
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- credit
 
-- Both succeed or neither do (Atomicity)
COMMIT;
 
-- On error:
ROLLBACK

-- Monthly sales per product to a pivot
SELECT
  product_id,
  SUM(CASE WHEN month = 1 THEN sales ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 2 THEN sales ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 3 THEN sales 

-- Find gaps in sequential order IDs
SELECT id + 1 AS gap_start, next_id - 1 AS gap_end
FROM (
  SELECT id, LEAD(id) OVER (ORDER BY id) AS next_id
  FROM orders
) t
WHERE next_id - id > 1;