SQL Mastery 2025: Developer Interview SQL — From JOINs to Window Functions and Query Optimization

┌──────────────────────────────────────────────────┐
│          Why SQL Is Still King (2025)             │
├──────────────────────────────────────────────────┤
│  Stack Overflow  → Top 3 most used language      │
│  PostgreSQL      → DBMS of the Year 2 years      │
│  AI/LLM          → Text-to-SQL core technology   │
│  Cloud            → Aurora, AlloyDB, Neon         │
│  Interview freq   → 80%+ of backend interviews   │
└──────────────────────────────────────────────────┘

-- Employees table
CREATE TABLE employees (
    id    INT PRIMARY KEY,
    name  VARCHAR(50),
    dept_id INT
);

INSERT INTO employees VALUES
(1, 'Alice',   10),
(2, 'Bob',     20),
(3, 'Charlie', 10),
(4, 'Diana',   30),
(5, 'Eve',     NULL);

-- Departments table
CREATE TABLE departments (
    id    INT PRIMARY KEY,
    name  VARCHAR(50)
);

INSERT INTO departments VALUES
(10, 'Engineering'),
(20, 'Marketing'),
(40, 'HR');

SELECT e.name AS employee, d.name AS department
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

┌──────────┬─────────────┐
│ employee │ department  │
├──────────┼─────────────┤
│ Alice    │ Engineering │
│ Bob      │ Marketing   │
│ Charlie  │ Engineering │
└──────────┴─────────────┘
-- Diana (dept_id=30) and Eve (dept_id=NULL) excluded - no match
-- HR (id=40) excluded - no matching employees

┌─────────┐   ┌─────────┐
│Employees│   │  Depts  │
│    ┌────┼───┼────┐    │
│    │████│███│████│    │
│    │████│███│████│    │
│    └────┼───┼────┘    │
└─────────┘   └─────────┘
      INNER JOIN = Intersection

SELECT e.name AS employee, d.name AS department
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

┌──────────┬─────────────┐
│ employee │ department  │
├──────────┼─────────────┤
│ Alice    │ Engineering │
│ Bob      │ Marketing   │
│ Charlie  │ Engineering │
│ Diana    │ NULL        │
│ Eve      │ NULL        │
└──────────┴─────────────┘

SELECT e.name AS employee, d.name AS department
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.id;

┌──────────┬─────────────┐
│ employee │ department  │
├──────────┼─────────────┤
│ Alice    │ Engineering │
│ Bob      │ Marketing   │
│ Charlie  │ Engineering │
│ NULL     │ HR          │
└──────────┴─────────────┘

SELECT e.name AS employee, d.name AS department
FROM employees e
FULL OUTER JOIN departments d ON e.dept_id = d.id;

┌──────────┬─────────────┐
│ employee │ department  │
├──────────┼─────────────┤
│ Alice    │ Engineering │
│ Bob      │ Marketing   │
│ Charlie  │ Engineering │
│ Diana    │ NULL        │
│ Eve      │ NULL        │
│ NULL     │ HR          │
└──────────┴─────────────┘

SELECT e.name, d.name
FROM employees e
CROSS JOIN departments d;
-- 5 employees x 3 departments = 15 rows

-- Manager-employee relationship
CREATE TABLE staff (
    id         INT PRIMARY KEY,
    name       VARCHAR(50),
    manager_id INT
);

INSERT INTO staff VALUES
(1, 'CEO',     NULL),
(2, 'CTO',     1),
(3, 'Alice',   2),
(4, 'Bob',     2);

SELECT
    s.name AS employee,
    m.name AS manager
FROM staff s
LEFT JOIN staff m ON s.manager_id = m.id;

┌──────────┬─────────┐
│ employee │ manager │
├──────────┼─────────┤
│ CEO      │ NULL    │
│ CTO      │ CEO     │
│ Alice    │ CTO     │
│ Bob      │ CTO     │
└──────────┴─────────┘

-- Employee count and average salary by department
SELECT
    dept_id,
    COUNT(*) AS emp_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary
FROM employees
GROUP BY dept_id
HAVING COUNT(*) >= 2
ORDER BY avg_salary DESC;

-- Department totals with grand total
SELECT
    COALESCE(dept_name, 'Grand Total') AS department,
    COALESCE(job_title, 'Subtotal') AS job,
    COUNT(*) AS emp_count,
    SUM(salary) AS total_salary
FROM employees e
JOIN departments d ON e.dept_id = d.id
GROUP BY ROLLUP(dept_name, job_title);

-- CUBE: subtotals for all combinations
SELECT dept_name, job_title, SUM(salary)
FROM employees e JOIN departments d ON e.dept_id = d.id
GROUP BY CUBE(dept_name, job_title);

-- GROUPING SETS: only specific combinations
SELECT dept_name, job_title, SUM(salary)
FROM employees e JOIN departments d ON e.dept_id = d.id
GROUP BY GROUPING SETS (
    (dept_name, job_title),  -- by dept + job
    (dept_name),              -- by dept
    ()                        -- grand total
);

-- GROUP BY: reduces rows (1 row per dept)
SELECT dept_id, AVG(salary) FROM employees GROUP BY dept_id;

-- Window Function: preserves rows (all employees + dept average)
SELECT
    name,
    dept_id,
    salary,
    AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
FROM employees;

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

┌─────────┬─────────┬────────┬─────────┬──────┬────────────┐
│  name   │ dept_id │ salary │ row_num │ rank │ dense_rank │
├─────────┼─────────┼────────┼─────────┼──────┼────────────┤
│ Alice   │   10    │ 120000 │    1    │  1   │     1      │
│ Charlie │   10    │ 120000 │    2    │  1   │     1      │
│ Bob     │   20    │ 100000 │    3    │  3   │     2      │
│ Diana   │   30    │  80000 │    4    │  4   │     3      │
│ Eve     │   20    │  80000 │    5    │  4   │     3      │
└─────────┴─────────┴────────┴─────────┴──────┴────────────┘

ROW_NUMBER: Always unique numbers (different even for ties)
RANK:       Same number for ties, gaps after (1, 1, 3)
DENSE_RANK: Same number for ties, no gaps   (1, 1, 2)

-- Divide into salary quartiles
SELECT
    name,
    salary,
    NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

-- Month-over-month revenue changes
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
    LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_sales;

┌───────┬─────────┬────────────┬────────┬────────────┐
│ month │ revenue │ prev_month │ change │ next_month │
├───────┼─────────┼────────────┼────────┼────────────┤
│ 01    │  50000  │   NULL     │  NULL  │   60000    │
│ 02    │  60000  │   50000    │  10000 │   45000    │
│ 03    │  45000  │   60000    │ -15000 │   70000    │
│ 04    │  70000  │   45000    │  25000 │   NULL     │
└───────┴─────────┴────────────┴────────┴────────────┘

SELECT
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_sum_3
FROM orders;

-- Top 3 salaries per department
WITH ranked AS (
    SELECT
        name,
        dept_id,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY dept_id
            ORDER BY salary DESC
        ) AS rn
    FROM employees
)
SELECT name, dept_id, salary
FROM ranked
WHERE rn <= 3;

-- Correlated subquery: employees earning above their dept average
SELECT name, salary, dept_id
FROM employees e1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees e2
    WHERE e2.dept_id = e1.dept_id
);

-- Non-correlated subquery: above overall average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

WITH dept_avg AS (
    SELECT dept_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY dept_id
),
high_earners AS (
    SELECT e.name, e.salary, e.dept_id, d.avg_salary
    FROM employees e
    JOIN dept_avg d ON e.dept_id = d.dept_id
    WHERE e.salary > d.avg_salary
)
SELECT * FROM high_earners ORDER BY salary DESC;

┌────────────────┬───────────────┬───────────────┬────────────────┐
│    Aspect       │  Subquery     │     CTE       │  Derived Table │
├────────────────┼───────────────┼───────────────┼────────────────┤
│ Readability    │ Low (nested)  │ High          │ Medium         │
│ Reuse          │ Not possible  │ Within query  │ Not possible   │
│ Recursion      │ Not possible  │ Yes (WITH REC)│ Not possible   │
│ Performance    │ DB-dependent  │ DB-dependent  │ Inline view    │
│ Debugging      │ Hard          │ Easy          │ Medium         │
└────────────────┴───────────────┴───────────────┴────────────────┘

CREATE TABLE org (
    id         INT PRIMARY KEY,
    name       VARCHAR(50),
    manager_id INT REFERENCES org(id)
);

INSERT INTO org VALUES
(1, 'CEO',      NULL),
(2, 'VP Eng',   1),
(3, 'VP Sales', 1),
(4, 'Dir Eng',  2),
(5, 'Lead Dev', 4),
(6, 'Dev 1',    5),
(7, 'Dev 2',    5);

-- Recursive CTE: traverse full org chart from CEO
WITH RECURSIVE org_tree AS (
    -- Base case: CEO (no manager)
    SELECT id, name, manager_id, 0 AS depth, name::TEXT AS path
    FROM org
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case: find subordinates
    SELECT o.id, o.name, o.manager_id,
           t.depth + 1,
           t.path || ' > ' || o.name
    FROM org o
    JOIN org_tree t ON o.manager_id = t.id
)
SELECT
    REPEAT('  ', depth) || name AS hierarchy,
    depth,
    path
FROM org_tree
ORDER BY path;

┌────────────────────┬───────┬──────────────────────────────┐
│ hierarchy          │ depth │ path                         │
├────────────────────┼───────┼──────────────────────────────┤
│ CEO                │   0   │ CEO                          │
│   VP Eng           │   1   │ CEO > VP Eng                 │
│     Dir Eng        │   2   │ CEO > VP Eng > Dir Eng       │
│       Lead Dev     │   3   │ CEO > VP Eng > Dir Eng > ... │
│         Dev 1      │   4   │ CEO > VP Eng > Dir Eng > ... │
│         Dev 2      │   4   │ CEO > VP Eng > Dir Eng > ... │
│   VP Sales         │   1   │ CEO > VP Sales               │
└────────────────────┴───────┴──────────────────────────────┘

-- Generate numbers 1 to 10
WITH RECURSIVE nums AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM nums WHERE n < 10
)
SELECT n FROM nums;

-- Date series generation
WITH RECURSIVE dates AS (
    SELECT DATE '2025-01-01' AS dt
    UNION ALL
    SELECT dt + INTERVAL '1 day' FROM dates WHERE dt < '2025-01-31'
)
SELECT dt FROM dates;

CREATE INDEX idx_employees_name ON employees(name);

-- Effective queries
SELECT * FROM employees WHERE name = 'Alice';            -- equality
SELECT * FROM employees WHERE name BETWEEN 'A' AND 'M';  -- range
SELECT * FROM employees ORDER BY name;                    -- sort
SELECT * FROM employees WHERE name LIKE 'Al%';           -- prefix

B-Tree structure:
              [M]
             /   \
          [D,H]  [P,T]
         /  |  \  / | \
       [A-C][E-G][I-L][N-O][Q-S][U-Z]
       (Leaf nodes: actual data pointers)

CREATE INDEX idx_employees_email_hash
ON employees USING HASH (email);

-- Effective
SELECT * FROM employees WHERE email = 'alice@example.com';

-- JSONB field indexing
CREATE INDEX idx_products_tags ON products USING GIN (tags);

SELECT * FROM products WHERE tags @> '["electronics"]';

-- Full-text search
CREATE INDEX idx_articles_search ON articles
USING GIN (to_tsvector('english', title || ' ' || body));

CREATE INDEX idx_locations_geom ON locations USING GiST (geom);

SELECT * FROM locations
WHERE ST_DWithin(geom, ST_MakePoint(127.0, 37.5)::geography, 1000);

-- Optimal for time-series data
CREATE INDEX idx_logs_created ON logs USING BRIN (created_at);

-- Column order matters!
CREATE INDEX idx_emp_dept_salary ON employees(dept_id, salary);

-- Effective: dept_id only, or dept_id + salary together
SELECT * FROM employees WHERE dept_id = 10;
SELECT * FROM employees WHERE dept_id = 10 AND salary > 50000;

-- Ineffective: salary only (skipping first column)
-- SELECT * FROM employees WHERE salary > 50000;

Composite Index ordering rules (Leftmost Prefix):

Index: (A, B, C)

WHERE A = ?                        -> Used
WHERE A = ? AND B = ?              -> Used
WHERE A = ? AND B = ? AND C = ?    -> Used
WHERE B = ?                        -> NOT Used (A missing)
WHERE B = ? AND C = ?              -> NOT Used (A missing)
WHERE A = ? AND C = ?              -> Only A used (B skipped)

EXPLAIN ANALYZE
SELECT e.name, d.name AS dept
FROM employees e
JOIN departments d ON e.dept_id = d.id
WHERE e.salary > 50000;

Hash Join  (cost=1.09..2.24 rows=3 width=36) (actual time=0.05..0.06 rows=3 loops=1)
  Hash Cond: (e.dept_id = d.id)
  ->  Seq Scan on employees e  (cost=0.00..1.12 rows=3 width=22)
        Filter: (salary > 50000)
        Rows Removed by Filter: 2
  ->  Hash  (cost=1.03..1.03 rows=3 width=18)
        ->  Seq Scan on departments d  (cost=0.00..1.03 rows=3 width=18)
Planning Time: 0.15 ms
Execution Time: 0.09 ms

┌──────────────────┬─────────────────────────────────────┐
│ Scan Type         │ Description                         │
├──────────────────┼─────────────────────────────────────┤
│ Seq Scan         │ Full table sequential scan           │
│ Index Scan       │ Index lookup + table access          │
│ Index Only Scan  │ Results from index only (no table)   │
│ Bitmap Index Scan│ Build bitmap from index, then table  │
│ Bitmap Heap Scan │ Table access based on bitmap         │
└──────────────────┴─────────────────────────────────────┘

cost=startup_cost..total_cost  rows=estimated_rows  width=row_size(bytes)

- startup_cost: cost until first row returned
- total_cost: total cost to return all rows
- actual time: real execution time (ms)
- rows: actual rows returned
- loops: number of times this node executed

┌──────────────────────────────────────────────────┐
│         Query Optimization Flow                   │
├──────────────────────────────────────────────────┤
│ 1. Run EXPLAIN ANALYZE                            │
│    |                                              │
│ 2. Seq Scan on large table?                       │
│    -> Consider adding index                       │
│    |                                              │
│ 3. Large gap between estimated and actual rows?   │
│    -> Run ANALYZE to update statistics            │
│    |                                              │
│ 4. Nested Loop on large datasets?                 │
│    -> Hash Join or Merge Join may be better       │
│    |                                              │
│ 5. Sort using disk?                               │
│    -> Increase work_mem or add index              │
└──────────────────────────────────────────────────┘

-- Bad: separate query per order (from application)
-- 1. SELECT * FROM orders WHERE user_id = 1;
-- 2. SELECT * FROM order_items WHERE order_id = 101;
-- 3. SELECT * FROM order_items WHERE order_id = 102;
-- ... (N orders = N+1 queries)

-- Good: single JOIN query
SELECT o.id, o.total, oi.product_name, oi.quantity
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.user_id = 1;

CREATE INDEX idx_emp_dept_name_salary
ON employees(dept_id, name, salary);

-- Index Only Scan possible (no table access needed)
SELECT name, salary FROM employees WHERE dept_id = 10;

-- Index only active users (most queries target active users)
CREATE INDEX idx_active_users
ON users(email)
WHERE status = 'active';

SELECT * FROM users WHERE status = 'active' AND email = 'alice@example.com';

-- Index for case-insensitive search
CREATE INDEX idx_users_lower_email
ON users(LOWER(email));

SELECT * FROM users WHERE LOWER(email) = 'alice@example.com';

-- JSON field indexing
CREATE INDEX idx_data_category
ON products((data->>'category'));

-- 1. EXISTS vs IN: EXISTS is usually faster for large datasets
-- Slower
SELECT * FROM orders WHERE customer_id IN (
    SELECT id FROM customers WHERE country = 'US'
);
-- Faster
SELECT * FROM orders o WHERE EXISTS (
    SELECT 1 FROM customers c
    WHERE c.id = o.customer_id AND c.country = 'US'
);

-- 2. LIMIT prevents unnecessary scanning
SELECT * FROM logs ORDER BY created_at DESC LIMIT 100;

-- 3. UNION ALL vs UNION: use ALL when dedup not needed
SELECT name FROM employees_2024
UNION ALL
SELECT name FROM employees_2025;

-- 4. Remove unnecessary ORDER BY inside subqueries
-- 5. SELECT specific columns instead of SELECT *

┌──────────────────┬───────────┬──────────────┬──────────────┬─────────────┐
│ Isolation Level   │Dirty Read │Non-Repeatable│Phantom Read  │ Performance │
│                  │           │    Read      │              │             │
├──────────────────┼───────────┼──────────────┼──────────────┼─────────────┤
│ Read Uncommitted │ Possible  │ Possible     │ Possible     │ Fastest     │
│ Read Committed   │ Prevented │ Possible     │ Possible     │ Fast        │
│ Repeatable Read  │ Prevented │ Prevented    │ Possible     │ Medium      │
│ Serializable     │ Prevented │ Prevented    │ Prevented    │ Slowest     │
└──────────────────┴───────────┴──────────────┴──────────────┴─────────────┘

PostgreSQL default: Read Committed
MySQL InnoDB default: Repeatable Read

-- Transaction A
UPDATE accounts SET balance = 0 WHERE id = 1;
-- (not yet committed)

-- Transaction B (Read Uncommitted)
SELECT balance FROM accounts WHERE id = 1;
-- balance = 0 (uncommitted value!)

-- Transaction A
ROLLBACK;
-- balance reverts to original, but Transaction B already used wrong data

-- Transaction A
SELECT balance FROM accounts WHERE id = 1;  -- 1000

-- Transaction B
UPDATE accounts SET balance = 500 WHERE id = 1;
COMMIT;

-- Transaction A (within same transaction)
SELECT balance FROM accounts WHERE id = 1;  -- 500 (different!)

-- Transaction A
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 5

-- Transaction B
INSERT INTO orders (status) VALUES ('pending');
COMMIT;

-- Transaction A
SELECT COUNT(*) FROM orders WHERE status = 'pending';  -- 6 (phantom row!)

┌──────────────────────────────────────────────────┐
│                  MVCC Principle                   │
├──────────────────────────────────────────────────┤
│                                                  │
│  Transaction 100 (Read):                         │
│  "I only see versions with xmin <= 100"          │
│                                                  │
│  Transaction 101 (Write):                        │
│  "Create new version with xmin=101,              │
│   mark old row with xmax=101"                    │
│                                                  │
│  -> Transaction 100 still sees the old version   │
│  -> Reads and writes do not block each other     │
│                                                  │
└──────────────────────────────────────────────────┘

CREATE TABLE products (
    id   SERIAL PRIMARY KEY,
    name TEXT,
    data JSONB
);

INSERT INTO products (name, data) VALUES
('Laptop', '{"brand": "Dell", "specs": {"ram": 16, "cpu": "i7"}, "tags": ["electronics", "computer"]}'),
('Phone',  '{"brand": "Apple", "specs": {"ram": 8, "cpu": "A17"}, "tags": ["electronics", "mobile"]}');

-- JSONB queries
SELECT name, data->>'brand' AS brand
FROM products
WHERE data->'specs'->>'ram' = '16';

-- Array containment
SELECT * FROM products WHERE data->'tags' @> '"mobile"';

-- JSONB path query (PostgreSQL 12+)
SELECT * FROM products
WHERE data @? '$.specs ? (@.ram > 10)';

CREATE TABLE articles (
    id   SERIAL PRIMARY KEY,
    title TEXT,
    tags  TEXT[]
);

INSERT INTO articles (title, tags) VALUES
('SQL Guide', ARRAY['sql', 'database', 'backend']),
('React Tips', ARRAY['react', 'frontend', 'javascript']);

-- Array containment search
SELECT * FROM articles WHERE tags @> ARRAY['sql'];
SELECT * FROM articles WHERE 'react' = ANY(tags);
SELECT * FROM articles WHERE tags && ARRAY['sql', 'react'];  -- overlap check

-- Get 3 most recent orders per department
SELECT d.name, recent.*
FROM departments d
CROSS JOIN LATERAL (
    SELECT o.id, o.amount, o.created_at
    FROM orders o
    WHERE o.dept_id = d.id
    ORDER BY o.created_at DESC
    LIMIT 3
) recent;

-- Pre-compute complex aggregation results
CREATE MATERIALIZED VIEW monthly_sales_summary AS
SELECT
    DATE_TRUNC('month', order_date) AS month,
    dept_id,
    COUNT(*) AS order_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM orders
GROUP BY DATE_TRUNC('month', order_date), dept_id;

-- Add index
CREATE INDEX idx_monthly_sales ON monthly_sales_summary(month, dept_id);

-- Refresh data
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_sales_summary;

SELECT * FROM A CROSS JOIN B;
-- A: 5 rows, B: 3 rows

SELECT name, salary,
       AVG(salary) OVER () AS dept_avg
FROM employees;
-- employees: Alice 100, Bob 200, Charlie 300