SQL Master

-- ROW_NUMBER: unique sequential integer, no ties
-- RANK: same rank for ties, gaps after ties
-- DENSE_RANK: same rank for ties, no gaps
SELECT
    employee_id,
    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_val,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank_val,
    NTILE(4)     OVER (PARTITION BY department ORDER BY salary DESC) AS quartile
FROM employees;

-- LAG/LEAD: access previous/next rows without self-join
SELECT
    order_date,
    revenue,
    LAG(revenue, 1)  OVER (ORDER BY order_date) AS prev_day_revenue,
    LEAD(revenue, 1) OVER (ORDER BY order_date) AS next_day_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY order_date) AS day_over_day_change,
    FIRST_VALUE(revenue) OVER (
        PARTITION BY DATE_TRUNC('month', order_date)
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_day_of_month_revenue
FROM daily_revenue;

-- Moving averages with precise frame control
SELECT
    trade_date,
    close_price,
    -- 7-day moving average
    AVG(close_price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS ma_7,
    -- 30-day moving average
    AVG(close_price) OVER (
        ORDER BY trade_date
        ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
    ) AS ma_30,
    # ... (condensed) ...
WHERE ticker = 'AAPL';

-- RANGE vs ROWS: RANGE groups identical ORDER BY values
-- ROWS treats each row independently
-- GROUPS (PostgreSQL 11+) counts distinct groups

-- Named subqueries for readability and reuse
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS total_sales
    FROM orders
    GROUP BY 1
),
monthly_growth AS (
    SELECT
        month,
        total_sales,
        LAG(total_sales) OVER (ORDER BY month) AS prev_month_sales,
        ROUND(
            (total_sales - LAG(total_sales) OVER (ORDER BY month))
            / LAG(total_sales) OVER (ORDER BY month) * 100, 2
        ) AS growth_pct
    FROM monthly_sales
)
SELECT * FROM monthly_growth WHERE growth_pct < 0;

-- Organizational hierarchy traversal
WITH RECURSIVE org_tree AS (
    -- Base case: top-level managers
    SELECT
        employee_id,
        name,
        manager_id,
        1 AS depth,
        ARRAY[employee_id] AS path,
        name::TEXT AS hierarchy
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL
# ... (condensed) ...
    SELECT dt + INTERVAL '1 day'
    FROM date_series
    WHERE dt < DATE '2024-12-31'
)
SELECT dt FROM date_series;

-- Find consecutive sequences (islands) and gaps
WITH numbered AS (
    SELECT
        event_date,
        event_date - (ROW_NUMBER() OVER (ORDER BY event_date))::INT * INTERVAL '1 day' AS grp
    FROM events
),
islands AS (
    SELECT
        MIN(event_date) AS island_start,
        MAX(event_date) AS island_end,
        COUNT(*) AS island_length
    FROM numbered
    GROUP BY grp
)
SELECT * FROM islands ORDER BY island_start;

-- PostgreSQL: always use ANALYZE for actual execution times
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.order_date > '2024-01-01';

-- Key metrics to examine:
-- 1. Seq Scan vs Index Scan (sequential = full table scan)
-- 2. Actual rows vs Estimated rows (>10x difference = stale statistics)
-- 3. Sort method: external merge = not enough work_mem
-- 4. Nested Loop vs Hash Join vs Merge Join
-- 5. Buffers: shared hit (cache) vs shared read (disk)

-- PostgreSQL: update statistics
ANALYZE table_name;

-- Check statistics accuracy
SELECT
    schemaname, tablename, n_live_tup, n_dead_tup,
    last_vacuum, last_autovacuum, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

-- Extended statistics for correlated columns (PostgreSQL 10+)
CREATE STATISTICS stats_name (dependencies)
ON column_a, column_b FROM table_name;

-- Column order matters: most selective first for equality,
-- range conditions last
-- Rule: equality columns first, then range columns, then sort columns

-- For query: WHERE status = 'active' AND created_at > '2024-01-01' ORDER BY name
CREATE INDEX idx_orders_status_created_name
ON orders (status, created_at, name);

-- Covering index: includes all columns needed, avoiding table lookup
CREATE INDEX idx_orders_covering
ON orders (customer_id)
INCLUDE (order_date, total_amount);

-- Partial index: index only relevant rows
CREATE INDEX idx_orders_active
ON orders (customer_id, order_date)
WHERE status = 'active';

-- Expression index
CREATE INDEX idx_users_lower_email
ON users (LOWER(email));

-- Find unused indexes (PostgreSQL)
SELECT
    schemaname, tablename, indexname,
    idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND indexrelid NOT IN (SELECT conindid FROM pg_constraint)
ORDER BY pg_relation_size(indexrelid) DESC;

-- Find missing indexes (tables with high sequential scans)
SELECT
    schemaname, relname,
    seq_scan, seq_tup_read,
    idx_scan, idx_tup_fetch,
    seq_tup_read / GREATEST(seq_scan, 1) AS avg_rows_per_seq_scan
FROM pg_stat_user_tables
WHERE seq_scan > 100
ORDER BY seq_tup_read DESC;

-- PostgreSQL pivot using FILTER
SELECT
    department,
    COUNT(*) FILTER (WHERE status = 'active')   AS active_count,
    COUNT(*) FILTER (WHERE status = 'inactive') AS inactive_count,
    COUNT(*) FILTER (WHERE status = 'pending')  AS pending_count
FROM employees
GROUP BY department;

-- Generic pivot using CASE
SELECT
    product_category,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1_revenue,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2_revenue,
    # ... (condensed) ...
SELECT p.product_id, v.quarter, v.revenue
FROM quarterly_products p
CROSS JOIN LATERAL (
    VALUES ('Q1', p.q1_rev), ('Q2', p.q2_rev), ('Q3', p.q3_rev), ('Q4', p.q4_rev)
) AS v(quarter, revenue);

-- PostgreSQL JSONB operations
-- Extract values
SELECT
    data->>'name'                    AS name_text,
    data->'address'->>'city'         AS city,
    data#>>'{address,zip}'           AS zip_alt_syntax,
    jsonb_array_length(data->'tags') AS tag_count
FROM users;

-- Query inside JSON
SELECT * FROM events
WHERE payload @> '{"type": "purchase"}'::jsonb;

-- Aggregate to JSON
# ... (condensed) ...
CROSS JOIN LATERAL jsonb_array_elements(o.items) AS item;

-- JSON path queries (PostgreSQL 12+)
SELECT * FROM events
WHERE payload @? '$.items[*] ? (@.price > 100)';

-- PostgreSQL full-text search setup
ALTER TABLE articles ADD COLUMN search_vector tsvector;

UPDATE articles SET search_vector =
    setweight(to_tsvector('english', COALESCE(title, '')), 'A') ||
    setweight(to_tsvector('english', COALESCE(abstract, '')), 'B') ||
    setweight(to_tsvector('english', COALESCE(body, '')), 'C');

CREATE INDEX idx_articles_fts ON articles USING GIN(search_vector);

-- Trigger for automatic updates
CREATE TRIGGER articles_search_update
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW EXECUTE FUNCTION
    # ... (condensed) ...
FROM articles,
     to_tsquery('english', 'machine & learning & !supervised') AS query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;

-- PostgreSQL: function with proper error handling
CREATE OR REPLACE FUNCTION transfer_funds(
    p_from_account BIGINT,
    p_to_account   BIGINT,
    p_amount       NUMERIC(15,2)
) RETURNS JSONB
LANGUAGE plpgsql
AS $$
DECLARE
    v_from_balance NUMERIC(15,2);
    v_result JSONB;
BEGIN
    -- Lock rows in consistent order to prevent deadlocks
    SELECT balance INTO v_from_balance
    # ... (condensed) ...
            'message', SQLERRM,
            'code', SQLSTATE
        );
END;
$$;

-- Anti-pattern: correlated subquery
SELECT * FROM orders o
WHERE (SELECT MAX(order_date) FROM orders o2 WHERE o2.customer_id = o.customer_id) = o.order_date;

-- Optimized: window function
SELECT * FROM (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
    FROM orders
) sub WHERE rn = 1;

-- Anti-pattern: count for existence
SELECT * FROM customers c
WHERE (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.id) > 0;

# ... (condensed) ...
JOIN orders o ON c.id = o.customer_id;

-- Optimized: semi-join
SELECT c.* FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Multiple aggregation levels in one pass
SELECT
    COALESCE(region, '(All Regions)') AS region,
    COALESCE(product, '(All Products)') AS product,
    SUM(revenue) AS total_revenue,
    GROUPING(region) AS is_region_total,
    GROUPING(product) AS is_product_total
FROM sales
GROUP BY GROUPING SETS (
    (region, product),  -- detail
    (region),           -- subtotal by region
    (product),          -- subtotal by product
    ()                  -- grand total
)
ORDER BY GROUPING(region), GROUPING(product), region, product;

-- Create materialized view for expensive aggregations
CREATE MATERIALIZED VIEW mv_daily_metrics AS
SELECT
    DATE_TRUNC('day', event_time) AS day,
    event_type,
    COUNT(*) AS event_count,
    COUNT(DISTINCT user_id) AS unique_users,
    AVG(duration_ms) AS avg_duration
FROM events
GROUP BY 1, 2
WITH DATA;

CREATE UNIQUE INDEX ON mv_daily_metrics (day, event_type);

-- Refresh concurrently (requires unique index, no lock on reads)
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_metrics;

-- Top-N per group without window functions
SELECT c.customer_name, recent_orders.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, order_date, total_amount
    FROM orders o
    WHERE o.customer_id = c.id
    ORDER BY order_date DESC
    LIMIT 3
) AS recent_orders;