SQL Interview Questions 2026: JOINs, Window Functions and Optimization

-- WHERE filters rows BEFORE grouping
SELECT department, COUNT(*) as count
FROM employees
WHERE salary > 50000     -- filters rows first
GROUP BY department;

-- HAVING filters groups AFTER GROUP BY
SELECT department, AVG(salary) as avg_salary
FROM employees
GROUP BY department
HAVING AVG(salary) > 60000;  -- filters groups

-- Key rule: WHERE cannot use aggregate functions
-- HAVING can use aggregate functions

-- Setup: users and orders tables
-- users: id, name
-- orders: id, user_id, total

-- INNER JOIN — only matching rows
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Returns only users who HAVE orders

-- LEFT JOIN — all from left, matching from right
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Returns ALL users (total=NULL if no orders)

-- RIGHT JOIN — all from right, matching from left
-- Rarely used (just flip LEFT JOIN)

-- FULL OUTER JOIN — all from both
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;

-- CROSS JOIN — every combination (cartesian product)
SELECT u.name, p.name
FROM users u
CROSS JOIN products p;  -- n x m rows

-- Index = B-tree data structure for fast lookups

-- When to create:
-- 1. Frequently queried columns (WHERE, JOIN ON, ORDER BY)
CREATE INDEX idx_users_email ON users(email);

-- 2. Compound index (for multi-column queries)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- 3. Unique constraint
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);

-- When NOT to create:
-- - Small tables (full scan may be faster)
-- - Frequently updated columns (updates must update index too)
-- - Low-cardinality columns (e.g., gender with 2 values)

-- Check if index is used
EXPLAIN SELECT * FROM users WHERE email = 'alice@example.com';

-- Method 1: LIMIT/OFFSET
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Method 2: Subquery
SELECT MAX(salary) as second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 3: Dense rank (best for Nth highest)
SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rank
    FROM employees
) ranked
WHERE rank = 2
LIMIT 1;

-- Find duplicates
SELECT email, COUNT(*) as count
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Show all duplicate rows
SELECT *
FROM users u1
WHERE EXISTS (
    SELECT 1 FROM users u2
    WHERE u2.email = u1.email
    AND u2.id != u1.id
);

-- Delete duplicates (keep lowest ID)
DELETE FROM users
WHERE id NOT IN (
    SELECT MIN(id)
    FROM users
    GROUP BY email
);

-- Window functions operate on a set of rows related to current row
SELECT
    name,
    department,
    salary,
    AVG(salary) OVER (PARTITION BY department) as dept_avg,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dept_rank,
    salary - LAG(salary) OVER (ORDER BY hire_date) as salary_change,
    SUM(salary) OVER (ORDER BY hire_date ROWS UNBOUNDED PRECEDING) as running_total
FROM employees;

-- ROW_NUMBER vs RANK vs DENSE_RANK
-- ROW_NUMBER: 1,2,3,4,5 (no ties)
-- RANK:       1,2,2,4,5 (ties get same rank, gap after)
-- DENSE_RANK: 1,2,2,3,4 (ties get same rank, no gap)

-- Common Table Expression (WITH clause)
WITH
monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total) AS revenue
    FROM orders
    GROUP BY 1
),
growth_rates AS (
    SELECT
        month,
        revenue,
        LAG(revenue) OVER (ORDER BY month) AS prev_month,
        ROUND(
            100.0 * (revenue - LAG(revenue) OVER (ORDER BY month)) /
            LAG(revenue) OVER (ORDER BY month), 2
        ) AS growth_pct
    FROM monthly_revenue
)
SELECT * FROM growth_rates WHERE month >= '2026-01-01';

-- Benefits: readability, reusability, no nested subquery hell
-- Recursive CTE: for hierarchical data (org charts, file trees)

EXPLAIN ANALYZE SELECT u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > NOW() - INTERVAL '30 days'
GROUP BY u.name
HAVING COUNT(o.id) > 5
ORDER BY order_count DESC;

-- Look for:
-- Seq Scan (sequential scan) on large table → needs index
-- Index Scan → using index (good)
-- Hash Join vs Nested Loop → Hash better for large tables
-- High cost estimates → consider query rewrite
-- rows= vs actual rows= discrepancy → run ANALYZE

-- N+1: 1 query for list, then 1 query per item
-- Bad (N+1 in application):
-- SELECT * FROM users              -- 1 query
-- SELECT * FROM orders WHERE user_id = 1  -- then N more queries!

-- Fix: JOIN to get data in one query
SELECT u.id, u.name, o.id as order_id, o.total
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.id IN (1, 2, 3);

-- Or use subquery for aggregates
SELECT u.id, u.name,
    (SELECT COUNT(*) FROM orders WHERE user_id = u.id) as order_count
FROM users u;