SQL Deep Dive

Beyond basic CRUD: Query optimization, execution plans, window functions, transactions, and interview patterns.

1. How Queries Execute

Understanding the query lifecycle helps you write better queries and debug performance issues.

Stage 1: Parser

Stage 2: Analyzer (Binder)

Stage 3: Optimizer

The most complex and important stage. The optimizer:

• Generates candidate plans - Different ways to execute the query
• Estimates costs - Using table statistics (row counts, value distributions)
• Chooses cheapest plan - Based on I/O, CPU, and memory costs

Stage 4: Executor

Executes the physical plan using a pull-based iterator model:

2. Indexing Deep Dive

B-Tree Index (Default)

The workhorse of database indexing. Keeps data sorted for efficient range queries.

When B-Tree Helps

-- Equality
SELECT * FROM users WHERE id = 42;

-- Range queries
SELECT * FROM orders WHERE created_at BETWEEN '2024-01-01' AND '2024-12-31';

-- Prefix matching
SELECT * FROM users WHERE name LIKE 'John%';

-- Sorting
SELECT * FROM products ORDER BY price LIMIT 10;

When B-Tree Doesn't Help

-- Leading wildcard (must scan all)
SELECT * FROM users WHERE name LIKE '%smith';

-- Functions on indexed column
SELECT * FROM users WHERE LOWER(email) = 'john@example.com';

-- OR conditions on different columns
SELECT * FROM users WHERE status = 'active' OR role = 'admin';

-- NOT conditions
SELECT * FROM users WHERE status != 'deleted';

Composite (Multi-Column) Indexes

-- Index on (country, city, zipcode)

-- Uses full index (all 3 columns)
WHERE country = 'USA' AND city = 'NYC' AND zipcode = '10001'

-- Uses first 2 columns
WHERE country = 'USA' AND city = 'NYC'

-- Uses first column only
WHERE country = 'USA'

-- CANNOT use index (missing leftmost column)
WHERE city = 'NYC'

Covering Indexes

When an index contains ALL columns needed by a query, it's a "covering index" - no table access needed.

-- Query
SELECT email, name FROM users WHERE status = 'active';

-- Covering index - includes all columns in query
CREATE INDEX idx_users_status_covering
ON users(status) INCLUDE (email, name);

-- PostgreSQL will show "Index Only Scan" in EXPLAIN

Partial (Filtered) Indexes

Index only a subset of rows. Smaller index = faster updates + fits in memory.

-- Only 5% of orders are pending, but we query them constantly
CREATE INDEX idx_orders_pending
ON orders(created_at)
WHERE status = 'pending';

-- Query must include the filter condition
SELECT * FROM orders
WHERE status = 'pending' AND created_at > NOW() - INTERVAL '1 day';

Hash Indexes

-- Only for equality comparisons, O(1) lookup
CREATE INDEX idx_users_email_hash ON users USING hash(email);

-- Good for
WHERE email = 'john@example.com'

-- Cannot use for ranges, ordering, or prefix matching

Index Types Comparison

3. Reading EXPLAIN Plans

EXPLAIN ANALYZE
SELECT u.name, COUNT(o.id)
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
GROUP BY u.id;

Key Metrics to Watch

Scan Types (Worst to Best)

-- Usually bad
Seq Scan on users
  Filter: (status = 'active')
  Rows Removed by Filter: 999000

-- Good for selective queries
Index Scan using idx_users_status
  Index Cond: (status = 'active')

-- Best - covering index
Index Only Scan using idx_covering
  Index Cond: (status = 'active')
  Heap Fetches: 0

-- Good for OR or multiple indexes
Bitmap Heap Scan on users
  -> BitmapOr
     -> Bitmap Index Scan
     -> Bitmap Index Scan

Common Performance Issues

4. Join Algorithms

The optimizer chooses join algorithms based on table sizes, indexes, and available memory.

Nested Loop Join

-- Good for nested loop: small outer, indexed inner
SELECT * FROM orders o          -- 100 rows (filtered)
JOIN products p ON o.product_id = p.id  -- Index on p.id
WHERE o.user_id = 42;

Hash Join

Merge Join (Sort-Merge)

Join Algorithm Comparison

*Plus sort cost if not pre-sorted

5. Window Functions

Window functions compute values across rows related to the current row without collapsing rows like GROUP BY.

Anatomy of a Window Function

function_name(...) OVER (
    PARTITION BY column      -- Groups rows (like GROUP BY but keeps all rows)
    ORDER BY column          -- Defines row order within partition
    ROWS BETWEEN ... AND ... -- Frame: which rows to include
)

Ranking Functions

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
    RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

Offset Functions: LAG and LEAD

-- Compare each sale to the previous day's sale
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day_change,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_day_revenue
FROM daily_sales;

Running Totals and Moving Averages

SELECT
    date,
    revenue,
    -- Running total
    SUM(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,

    -- 7-day moving average
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7d
FROM daily_sales;

Frame Specifications

FIRST_VALUE, LAST_VALUE, NTH_VALUE

SELECT
    department,
    name,
    salary,
    FIRST_VALUE(name) OVER w AS highest_paid,
    LAST_VALUE(name) OVER w AS lowest_paid,
    salary - FIRST_VALUE(salary) OVER w AS diff_from_top
FROM employees
WINDOW w AS (
    PARTITION BY department
    ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);

6. CTEs and Recursive Queries

Common Table Expressions (WITH clause)

WITH
    monthly_totals AS (
        SELECT
            DATE_TRUNC('month', order_date) AS month,
            SUM(total) AS revenue
        FROM orders
        GROUP BY 1
    ),
    with_growth AS (
        SELECT
            month,
            revenue,
            LAG(revenue) OVER (ORDER BY month) AS prev_revenue
        FROM monthly_totals
    )
SELECT
    month,
    revenue,
    ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM with_growth;

Recursive CTEs - Hierarchical Data

-- Employee org chart: find all reports under a manager
WITH RECURSIVE org_chart AS (
    -- Base case: the starting manager
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1  -- CEO

    UNION ALL

    -- Recursive case: find direct reports
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

Recursive CTE - Graph Traversal

-- Find all paths from node A to node F
WITH RECURSIVE paths AS (
    -- Start at node A
    SELECT
        source,
        target,
        ARRAY[source, target] AS path,
        weight
    FROM edges
    WHERE source = 'A'

    UNION ALL

    -- Extend paths
    SELECT
        p.source,
        e.target,
        p.path || e.target,
        p.weight + e.weight
    FROM paths p
    JOIN edges e ON p.target = e.source
    WHERE NOT e.target = ANY(p.path)  -- Prevent cycles
)
SELECT path, weight
FROM paths
WHERE target = 'F'
ORDER BY weight;

7. Transactions & Isolation Levels

ACID Properties

Isolation Levels and Anomalies

Read Phenomena

T1: UPDATE balance = 0
T2: SELECT balance → 0  # Dirty!
T1: ROLLBACK
# T2 saw data that never existed

T1: SELECT balance → 100
T2: UPDATE balance = 50
T2: COMMIT
T1: SELECT balance → 50  # Changed!

T1: SELECT COUNT(*) WHERE age>21 → 5
T2: INSERT (age=25)
T2: COMMIT
T1: SELECT COUNT(*) WHERE age>21 → 6

T1: SELECT balance → 100
T2: SELECT balance → 100
T1: UPDATE balance = 100 + 50
T2: UPDATE balance = 100 + 30
# Expected: 180, Actual: 130

Isolation Level Matrix

*PostgreSQL's Repeatable Read prevents phantoms; MySQL's does not.

MVCC - How Isolation Works

8. Locking & Concurrency

Lock Types

SELECT FOR UPDATE - Pessimistic Locking

BEGIN;

-- Lock the row, preventing other transactions from modifying it
SELECT balance FROM accounts
WHERE id = 1
FOR UPDATE;

-- Safe to update - no one else can change it
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

COMMIT;

FOR UPDATE Variants

-- Wait for lock (default)
SELECT * FROM orders WHERE id = 1 FOR UPDATE;

-- Error immediately if locked
SELECT * FROM orders WHERE id = 1 FOR UPDATE NOWAIT;

-- Skip locked rows (great for job queues)
SELECT * FROM jobs WHERE status = 'pending'
LIMIT 1 FOR UPDATE SKIP LOCKED;

Optimistic Locking

-- Add version column to table
ALTER TABLE products ADD COLUMN version INT DEFAULT 1;

-- Read current state
SELECT id, name, price, version FROM products WHERE id = 42;
-- Returns: version = 5

-- Update only if version unchanged
UPDATE products
SET price = 99.99, version = version + 1
WHERE id = 42 AND version = 5;

-- If 0 rows affected → someone else modified → retry

Deadlocks

-- Lock multiple rows in consistent order
SELECT * FROM accounts
WHERE id IN (3, 5, 7)
ORDER BY id  -- Consistent ordering!
FOR UPDATE;

9. Interview SQL Patterns

Pattern 1: Top N Per Group

Problem: Get top 3 highest paid employees per department.

-- Using window function (most common approach)
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY department_id
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

-- Alternative: LATERAL join (PostgreSQL)
SELECT d.name, e.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT * FROM employees
    WHERE department_id = d.id
    ORDER BY salary DESC
    LIMIT 3
) e;

Pattern 2: Running Total / Cumulative Sum

SELECT
    date,
    amount,
    SUM(amount) OVER (
        ORDER BY date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM transactions;

Pattern 3: Gaps and Islands

Problem: Find consecutive date ranges from sparse data.

-- Find islands of consecutive dates
WITH grouped AS (
    SELECT
        date,
        date - (ROW_NUMBER() OVER (ORDER BY date))::int AS grp
    FROM events
)
SELECT
    MIN(date) AS island_start,
    MAX(date) AS island_end,
    COUNT(*) AS consecutive_days
FROM grouped
GROUP BY grp
ORDER BY island_start;

Pattern 4: Pivot / Transpose

-- Transform rows to columns
SELECT
    user_id,
    SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS jan_revenue,
    SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS feb_revenue,
    SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS mar_revenue
FROM monthly_sales
GROUP BY user_id;

-- PostgreSQL crosstab (requires tablefunc extension)
SELECT * FROM crosstab(
    'SELECT user_id, month, revenue FROM monthly_sales ORDER BY 1,2'
) AS ct(user_id INT, jan NUMERIC, feb NUMERIC, mar NUMERIC);

Pattern 5: Find Duplicates

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

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

-- Better: Using CTE with window function
WITH ranked 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 ranked WHERE rn > 1);

Pattern 6: Median

-- PostgreSQL has built-in percentile functions
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)
FROM employees;

-- Manual approach (works in most databases)
WITH ordered AS (
    SELECT
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS rn,
        COUNT(*) OVER () AS total
    FROM employees
)
SELECT AVG(salary) AS median
FROM ordered
WHERE rn IN (total/2, total/2 + 1)
   OR (total % 2 = 1 AND rn = (total + 1) / 2);

Pattern 7: Consecutive Events

Problem: Find users who logged in 3+ consecutive days.

WITH login_groups AS (
    SELECT
        user_id,
        login_date,
        login_date - ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY login_date
        )::int AS grp
    FROM logins
    GROUP BY user_id, login_date  -- Dedupe same-day logins
)
SELECT user_id, COUNT(*) AS consecutive_days
FROM login_groups
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

Pattern 8: Self Join for Comparisons

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

Pattern 9: NOT EXISTS vs NOT IN

-- Find customers who never ordered

-- NOT IN: BEWARE of NULLs! Returns empty if any NULL in subquery
SELECT * FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- NOT EXISTS: NULL-safe, often faster
SELECT * FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- LEFT JOIN / IS NULL: Also NULL-safe
SELECT c.* FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

Pattern 10: Date Manipulation

-- Truncate to start of period
SELECT DATE_TRUNC('month', created_at) AS month_start FROM orders;

-- Generate date series (for filling gaps)
SELECT generate_series(
    '2024-01-01'::date,
    '2024-12-31'::date,
    '1 day'::interval
) AS date;

-- Join with date series to include zero-count days
WITH dates AS (
    SELECT generate_series('2024-01-01', '2024-01-31', '1 day'::interval)::date AS d
)
SELECT d.d, COALESCE(COUNT(o.id), 0) AS order_count
FROM dates d
LEFT JOIN orders o ON d.d = o.order_date::date
GROUP BY d.d
ORDER BY d.d;

Quick Reference: Performance Checklist

← Back to Index