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.

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

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 a group

SELECT *
FROM (
  SELECT
    user_id,
    session_id,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY started_at DESC) AS rn
  FROM sessions
)
WHERE rn = 1; -- latest session per user

Use ROW_NUMBER() when you need exactly one row. Use RANK() when ties should share a rank. Use DENSE_RANK() when there should be no gaps after ties.

Lag / Lead comparisons

SELECT
  user_id,
  event_date,
  LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date) AS prev_event_date,
  DATE_DIFF(event_date, LAG(event_date) OVER (PARTITION BY user_id ORDER BY event_date), DAY) AS days_since_prev
FROM events;

Tip: define the window once with a named window clause to avoid repeating it:

SELECT
  user_id,
  event_date,
  LAG(event_date)  OVER w AS prev_date,
  LEAD(event_date) OVER w AS next_date
FROM events
WINDOW w AS (PARTITION BY user_id ORDER BY event_date);

Deduplication

The safest way to deduplicate is ROW_NUMBER(), not DISTINCT — DISTINCT works only when every column is identical.

WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id, event_type ORDER BY created_at DESC) AS rn
  FROM raw_events
)
SELECT * EXCEPT (rn) FROM deduped WHERE rn = 1;

Query Optimization Patterns

Predicate pushdown — filter early

Always filter before joining. Move WHERE conditions as close to the raw table as possible, ideally inside a CTE.

-- Good: filter before joining
WITH recent_orders AS (
  SELECT * FROM orders WHERE order_date >= '2024-01-01'
)
SELECT u.name, o.amount FROM users u JOIN recent_orders o USING (user_id);

-- Bad: filter after join
SELECT u.name, o.amount
FROM users u JOIN orders o USING (user_id)
WHERE o.order_date >= '2024-01-01';

Partition pruning on BigQuery / Snowflake

Always filter on the partition column when it exists. On BigQuery, this is usually a DATE or TIMESTAMP column declared in PARTITION BY. Without it, the query scans the entire table.

-- Include partition filter to prune scan
WHERE DATE(created_at) BETWEEN '2024-01-01' AND '2024-03-31'

Avoid SELECT * in production

Columnar warehouses charge/measure by bytes scanned. Name every column you actually need.

Use APPROX_COUNT_DISTINCT for cardinality estimation

When exact uniqueness counts aren't required (e.g., dashboards), APPROX_COUNT_DISTINCT is 10-100x faster and within ~1% accuracy.

Anti-Patterns to Avoid

Idiomatic Patterns by Warehouse

BigQuery

• Use DATE_TRUNC, TIMESTAMP_TRUNC for time bucketing
• Prefer UNNEST over string splitting
• Use QUALIFY for post-window filtering instead of a wrapper CTE when available

Snowflake

• Use FLATTEN for semi-structured JSON
• Use QUALIFY ROW_NUMBER() OVER (...) = 1 directly in the SELECT

DuckDB (local / dbt dev)

• EXCLUDE and REPLACE column modifiers reduce boilerplate
• PIVOT / UNPIVOT are native
• Read Parquet/CSV directly with read_parquet() or read_csv_auto()

Query Review Checklist

Before finalizing a query, verify:

• [ ] CTEs named descriptively, one responsibility each
• [ ] Partition/cluster columns present in WHERE
• [ ] No correlated subqueries
• [ ] Window functions use explicit ROWS BETWEEN or RANGE BETWEEN when order matters
• [ ] Deduplication logic is intentional (row number vs. distinct)
• [ ] Join keys have matching types
• [ ] No SELECT * in final output unless prototyping