sql-patternslisted
Install: claude install-skill Methasit-Pun/data_engineer_claude_skills
# SQL Patterns for Analytics
## When to use this skill
Reach for these patterns any time you're writing transformations, aggregations, or analytical queries on a columnar warehouse (BigQuery, Snowflake, Redshift, DuckDB, Spark SQL). The goal is SQL that is readable, correct, and runs efficiently at scale — not just SQL that produces the right answer on a small sample.
---
## CTEs — Structure First, Optimize Later
Break complex logic into named stages. Each CTE should do one thing and have a name that reads like a sentence fragment explaining what it holds.
```sql
WITH
active_users AS (
SELECT user_id, MAX(event_time) AS last_seen
FROM events
WHERE event_date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY user_id
),
churned AS (
SELECT u.user_id
FROM users u
LEFT JOIN active_users a USING (user_id)
WHERE a.user_id IS NULL
)
SELECT * FROM churned;
```
**Why this matters:** Deep subqueries collapse context. A CTE chain lets any reader (including future you) audit each stage independently. Optimizers on modern warehouses inline CTEs anyway — readability is free.
---
## Window Functions
### Running totals and moving averages
```sql
SELECT
event_date,
revenue,
SUM(revenue) OVER (ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue,
AVG(revenue) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS revenue_7d_avg
FROM daily_revenue;
```
### Ranking within