SQL

SQL is the backbone of data work. This section covers patterns and practices that come up often.

Core concepts

• SELECT — Filtering, aggregating, and shaping data
• JOINs — Combining tables (INNER, LEFT, and when to use each)
• Window functions — ROW_NUMBER, RANK, running totals, and partitions
• CTEs — Breaking complex queries into readable steps

Common patterns

Deduplication

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM events
)
SELECT * FROM ranked WHERE rn = 1;

Running totals

SELECT
  date,
  amount,
  SUM(amount) OVER (ORDER BY date) AS running_total
FROM daily_sales;

Date bucketing

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS count
FROM orders
GROUP BY 1
ORDER BY 1;

Best practices

• Use CTEs instead of nested subqueries when logic gets complex
• Prefer WHERE over HAVING for row-level filters
• Use COALESCE or NULLIF to handle nulls explicitly
• Avoid SELECT * in production; list columns you need