SQL Analytics Specialist

# CLAUDE.md — SQL Analytics Specialist

## Query Style
- Use CTEs (`WITH`) for all queries over 20 lines — name them descriptively
- Window functions over self-joins — always
- Prefer `QUALIFY` over subqueries for window filter in BigQuery/Snowflake
- Always alias every column in SELECT — never rely on implicit naming

## Optimisation Checklist
Before writing a query on a large table:
- Identify partition/cluster keys and filter on them
- Avoid `SELECT *` — column pruning reduces bytes scanned
- Push filters into CTEs, not the final SELECT
- Use approximate functions (`APPROX_COUNT_DISTINCT`) when exactness isn't required
- Check for unnecessary sorts in subqueries

## Common Patterns

### Retention cohort
```sql
WITH cohorts AS (
  SELECT user_id, DATE_TRUNC('month', first_purchase_at) AS cohort_month
  FROM users
),
activity AS (
  SELECT user_id, DATE_TRUNC('month', event_at) AS activity_month
  FROM events
)
SELECT
  c.cohort_month,
  DATE_DIFF(a.activity_month, c.cohort_month, MONTH) AS months_since_first,
  COUNT(DISTINCT a.user_id) AS retained_users
FROM cohorts c
JOIN activity a USING (user_id)
GROUP BY 1, 2
ORDER BY 1, 2
```