SQL Advanced Core Knowledge

Deep Knowledge: Use mcp__documentation__fetch_docs with technology: sql for comprehensive documentation.

Common Table Expressions (CTEs)

Basic CTE

WITH active_users AS ( SELECT id, name, email FROM users WHERE status = 'active' ) SELECT u.name, COUNT(o.id) as order_count FROM active_users u LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id, u.name;

Multiple CTEs

WITH active_users AS ( SELECT id, name FROM users WHERE status = 'active' ), user_orders AS ( SELECT user_id, COUNT() as order_count, SUM(total) as total_spent FROM orders WHERE status = 'completed' GROUP BY user_id ), high_value_users AS ( SELECT u., uo.order_count, uo.total_spent FROM active_users u JOIN user_orders uo ON uo.user_id = u.id WHERE uo.total_spent > 10000 ) SELECT * FROM high_value_users ORDER BY total_spent DESC;

Recursive CTEs

-- Hierarchical data (org chart, categories) WITH RECURSIVE org_chart AS ( -- Base case: top-level employees SELECT id, name, manager_id, 1 as level, ARRAY[name] as path FROM employees WHERE manager_id IS NULL

UNION ALL

-- Recursive case
SELECT e.id, e.name, e.manager_id, oc.level + 1, oc.path || e.name
FROM employees e
JOIN org_chart oc ON e.manager_id = oc.id

) SELECT * FROM org_chart ORDER BY path;

-- Generate series WITH RECURSIVE numbers AS ( SELECT 1 as n UNION ALL SELECT n + 1 FROM numbers WHERE n < 100 ) SELECT * FROM numbers;

-- Date range WITH RECURSIVE dates AS ( SELECT DATE '2024-01-01' as date UNION ALL SELECT date + INTERVAL '1 day' FROM dates WHERE date < '2024-12-31' ) SELECT * FROM dates;

Materialized CTE (PostgreSQL 12+)

-- Force CTE to be materialized (evaluated once) WITH active_users AS MATERIALIZED ( SELECT * FROM users WHERE status = 'active' ) SELECT * FROM active_users WHERE id = 1 UNION ALL SELECT * FROM active_users WHERE id = 2;

-- Force CTE to be inlined (not materialized) WITH active_users AS NOT MATERIALIZED ( SELECT * FROM users WHERE status = 'active' ) SELECT * FROM active_users WHERE id = 1;

Window Functions Deep Dive

Partitioned Calculations

SELECT department, name, salary, -- Within department SUM(salary) OVER (PARTITION BY department) as dept_total, AVG(salary) OVER (PARTITION BY department) as dept_avg, salary - AVG(salary) OVER (PARTITION BY department) as diff_from_avg, -- Percentage of department total ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) as pct_of_dept FROM employees;

Running Calculations

SELECT date, amount, -- Running totals SUM(amount) OVER (ORDER BY date) as running_total, SUM(amount) OVER (ORDER BY date ROWS UNBOUNDED PRECEDING) as same_as_above, -- Moving averages AVG(amount) OVER (ORDER BY date ROWS 6 PRECEDING) as moving_avg_7d, AVG(amount) OVER (ORDER BY date ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING) as centered_avg, -- Cumulative stats COUNT(*) OVER (ORDER BY date) as cumulative_count, MIN(amount) OVER (ORDER BY date) as running_min, MAX(amount) OVER (ORDER BY date) as running_max FROM daily_transactions;

Gap and Island Analysis

-- Find consecutive sequences (islands) WITH numbered AS ( SELECT date, value, ROW_NUMBER() OVER (ORDER BY date) as rn, date - (ROW_NUMBER() OVER (ORDER BY date) * INTERVAL '1 day') as grp FROM daily_data ) SELECT MIN(date) as island_start, MAX(date) as island_end, COUNT(*) as days_in_sequence FROM numbered GROUP BY grp ORDER BY island_start;

-- Find gaps in sequence SELECT id, LEAD(id) OVER (ORDER BY id) as next_id, LEAD(id) OVER (ORDER BY id) - id - 1 as gap_size FROM items WHERE LEAD(id) OVER (ORDER BY id) - id > 1;

First/Last in Group

-- Get first and last values per group SELECT DISTINCT ON (department) department, name as highest_paid, salary FROM employees ORDER BY department, salary DESC;

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

Query Optimization

EXPLAIN Basics

-- Show query plan EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Show actual execution stats EXPLAIN ANALYZE SELECT * FROM users WHERE email = 'test@example.com';

-- More details EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT) SELECT * FROM users WHERE email = 'test@example.com';

-- JSON output for tools EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON) SELECT * FROM users WHERE email = 'test@example.com';

Reading EXPLAIN Output

Seq Scan on users (cost=0.00..155.00 rows=1 width=100) (actual time=0.015..0.842 rows=1 loops=1) Filter: (email = 'test@example.com'::text) Rows Removed by Filter: 4999

Term Meaning

Seq Scan

Full table scan (often bad)

Index Scan

Using index (good)

Index Only Scan

Using covering index (best)

Bitmap Index Scan

Using multiple indexes

cost=0.00..155.00

Estimated startup..total cost

rows=1

Estimated rows returned

actual time=0.015..0.842

Real startup..total time (ms)

Rows Removed by Filter

Rows read but not returned

Common Performance Issues

Missing Index

-- Problem: Seq Scan EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Solution: Add index CREATE INDEX idx_users_email ON users(email);

Index Not Used

-- Problem: Function on column prevents index use SELECT * FROM users WHERE LOWER(email) = 'test@example.com';

-- Solution: Expression index CREATE INDEX idx_users_email_lower ON users(LOWER(email));

N+1 Query Problem

-- Problem: Querying in a loop (application code) -- For each user: SELECT * FROM orders WHERE user_id = ?

-- Solution: Single query with JOIN SELECT u., o. FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE u.id IN (1, 2, 3, 4, 5);

Index Strategies

-- Composite index (column order matters!) -- Good for: WHERE a = ? AND b = ? -- Good for: WHERE a = ? ORDER BY b CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Covering index (all needed columns in index) CREATE INDEX idx_orders_user_covering ON orders(user_id) INCLUDE (status, total);

-- Partial index (filtered) CREATE INDEX idx_active_orders ON orders(user_id) WHERE status = 'active';

-- Expression index CREATE INDEX idx_orders_year ON orders(EXTRACT(YEAR FROM created_at));

Query Rewriting

-- Avoid: Subquery in SELECT SELECT name, (SELECT COUNT(*) FROM orders WHERE user_id = users.id) as order_count FROM users;

-- Better: JOIN with aggregation SELECT u.name, COALESCE(o.order_count, 0) as order_count FROM users u LEFT JOIN ( SELECT user_id, COUNT(*) as order_count FROM orders GROUP BY user_id ) o ON o.user_id = u.id;

-- Avoid: OR conditions on different columns SELECT * FROM users WHERE email = 'a@b.com' OR phone = '123';

-- Better: UNION (can use indexes) SELECT * FROM users WHERE email = 'a@b.com' UNION SELECT * FROM users WHERE phone = '123';

-- Avoid: NOT IN with NULLs (tricky behavior) SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned);

-- Better: NOT EXISTS SELECT * FROM users u WHERE NOT EXISTS ( SELECT 1 FROM banned b WHERE b.user_id = u.id );

Advanced Patterns

Pivot/Unpivot

-- Pivot: Rows to columns SELECT product_id, SUM(CASE WHEN month = 1 THEN sales END) as jan, SUM(CASE WHEN month = 2 THEN sales END) as feb, SUM(CASE WHEN month = 3 THEN sales END) as mar FROM monthly_sales GROUP BY product_id;

-- PostgreSQL: crosstab SELECT * FROM crosstab( 'SELECT product_id, month, sales FROM monthly_sales ORDER BY 1,2' ) AS ct(product_id INT, jan INT, feb INT, mar INT);

-- Unpivot: Columns to rows (PostgreSQL) SELECT product_id, month, sales FROM products, LATERAL (VALUES ('jan', jan_sales), ('feb', feb_sales), ('mar', mar_sales) ) AS t(month, sales);

De-duplication

-- Keep first occurrence DELETE FROM users a USING users b WHERE a.id > b.id AND a.email = b.email;

-- With CTE (safer) WITH duplicates AS ( SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at) as rn FROM users ) DELETE FROM users WHERE id IN ( SELECT id FROM duplicates WHERE rn > 1 );

Temporal Queries

-- Events active at specific time SELECT * FROM events WHERE start_time <= '2024-06-15 10:00:00' AND end_time > '2024-06-15 10:00:00';

-- Overlapping periods SELECT a., b. FROM reservations a, reservations b WHERE a.id < b.id AND a.room_id = b.room_id AND a.start_time < b.end_time AND a.end_time > b.start_time;

-- Fill gaps with generate_series SELECT d.date, COALESCE(s.revenue, 0) as revenue FROM generate_series( '2024-01-01'::date, '2024-12-31'::date, '1 day' ) d(date) LEFT JOIN daily_sales s ON s.date = d.date;

When NOT to Use This Skill

• Basic SQL (SELECT, JOIN, INSERT) - Use sql-fundamentals skill

• PostgreSQL specifics (arrays, JSONB) - Use postgresql skill

• MySQL specifics (stored procedures) - Use mysql skill

• ORM queries - Use prisma , typeorm , or relevant ORM skill

Anti-Patterns

Anti-Pattern Problem Solution

Correlated subqueries Slow performance, N+1 Use JOINs or window functions

Functions on indexed columns Index not used Use functional indexes

Deep recursion without limit Stack overflow Add recursion depth limit

Missing WHERE in CTEs Processes unnecessary data Filter early in CTEs

Over-using window functions Memory pressure Limit result set first

Not analyzing EXPLAIN output Slow queries go unnoticed Always check execution plans

Quick Troubleshooting

Problem Diagnostic Fix

Slow CTE execution EXPLAIN ANALYZE Add MATERIALIZED hint or rewrite

High memory usage Check sort/hash operations Increase work_mem, optimize query

Recursion limit exceeded Check recursion depth Add LIMIT, redesign query

Window function slow Check PARTITION BY cardinality Add indexes on partition columns

Query plan changes Compare EXPLAIN outputs Update statistics, pin plan

Reference Documentation

• CTEs and Recursive Queries

• Window Functions

• Recursive Queries

• Query Optimization