# SQL Advanced Patterns

> Use when writing advanced SQL queries and optimizations.

- Skill: `loopyluci/sql-advanced-patterns` (Agent Skill)
- Install (CLI): `npx skillmds@latest add loopyluci/sql-advanced-patterns`
- Raw SKILL.md: https://api.skillmd.com/api/skills/loopyluci/sql-advanced-patterns/raw
- Safety review: pending (external: skill-scanner PASS, skillspector PASS)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: LoopyLuci (https://skillmd.com/u/loopyluci)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/loopyluci/sql-advanced-patterns

---


# Advanced SQL Patterns

Writing advanced SQL queries — from CTEs and window functions through query optimization, execution plan analysis, and performance tuning.

## When to Use

- Writing complex analytical SQL queries
- Optimizing slow-running queries
- Using window functions for running totals, ranking, moving averages
- Recursive CTEs for hierarchical data
- Understanding and improving query execution plans

## SQL Patterns

```sql
-- Window function: running total
SELECT date, amount,
       SUM(amount) OVER (ORDER BY date) as running_total,
       AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d
FROM transactions;

-- Recursive CTE: org hierarchy
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level;

-- Pivot with conditional aggregation
SELECT 
    department,
    COUNT(*) FILTER (WHERE status = 'active') as active,
    COUNT(*) FILTER (WHERE status = 'inactive') as inactive
FROM employees GROUP BY department;
```

## Verification Checklist

- [ ] Window functions avoid self-joins for running totals
- [ ] CTEs improve readability and maintainability
- [ ] Queries use indexes effectively (check EXPLAIN ANALYZE)
- [ ] No N+1 queries (batched with IN or JOIN)
- [ ] Recursive CTEs have termination condition
- [ ] Query execution time under acceptable threshold