SQL for Data

Why SQL for Data

SQL (Structured Query Language) is the lingua franca of data. Whether the data lives in Postgres, MySQL, Snowflake, BigQuery, or DuckDB, the same core dialect lets you filter, aggregate, join, and reshape millions of rows declaratively. You describe what you want; the query planner figures out how to get it.

This guide assumes a simple analytics schema: an orders table, a customers table, and a products table.

SELECT, WHERE, ORDER BY

Every query starts with projecting columns and filtering rows. WHERE filters before aggregation, and ORDER BY sorts the final result.

SELECT
    order_id,
    customer_id,
    total_amount,
    created_at
FROM orders
WHERE status = 'completed'
  AND total_amount > 100
  AND created_at >= '2026-01-01'
ORDER BY total_amount DESC
LIMIT 20;

Useful predicates: IN (...), BETWEEN a AND b, LIKE '%abc%', and IS NULL / IS NOT NULL. Remember that NULL = NULL is never true — always use IS NULL.

Aggregations with GROUP BY and HAVING

Aggregate functions collapse many rows into one summary row per group. HAVING filters after aggregation, whereas WHERE filters before it.

SELECT
    customer_id,
    COUNT(*)          AS order_count,
    SUM(total_amount) AS lifetime_value,
    AVG(total_amount) AS avg_order,
    MAX(created_at)   AS last_order
FROM orders
WHERE status = 'completed'
GROUP BY customer_id
HAVING SUM(total_amount) > 1000
ORDER BY lifetime_value DESC;

JOINs

Joins combine rows from multiple tables based on a matching condition. The join type controls what happens to rows with no match.

SELECT
    c.name,
    o.order_id,
    o.total_amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
    AND o.status = 'completed'
ORDER BY c.name;

A LEFT JOIN with a right-side IS NULL filter is the classic "find rows with no match" (anti-join) pattern:

SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;  -- customers who never ordered

Subqueries

A subquery is a query nested inside another. It can appear in WHERE, FROM, or SELECT.

-- Orders larger than the overall average
SELECT order_id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);

-- Correlated subquery: customers with above-average spend for their region
SELECT c.name
FROM customers c
WHERE c.lifetime_value > (
    SELECT AVG(c2.lifetime_value)
    FROM customers c2
    WHERE c2.region = c.region
);

Common Table Expressions (CTEs)

CTEs use WITH to name intermediate result sets, making complex queries readable and composable. Chain several with commas.

WITH monthly_revenue AS (
    SELECT
        DATE_TRUNC('month', created_at) AS month,
        SUM(total_amount)               AS revenue
    FROM orders
    WHERE status = 'completed'
    GROUP BY DATE_TRUNC('month', created_at)
),
ranked AS (
    SELECT month, revenue,
           RANK() OVER (ORDER BY revenue DESC) AS rk
    FROM monthly_revenue
)
SELECT month, revenue
FROM ranked
WHERE rk <= 3;  -- top 3 months by revenue

Window Functions

Window functions compute values across a set of rows related to the current row without collapsing them like GROUP BY does. The OVER() clause defines the window.

SELECT
    customer_id,
    order_id,
    total_amount,
    created_at,
    -- one distinct rank per row within each customer
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS seq,
    -- ties share a rank, gaps after ties
    RANK()       OVER (PARTITION BY customer_id ORDER BY total_amount DESC) AS spend_rank,
    -- previous / next order value for the same customer
    LAG(total_amount)  OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_amount,
    LEAD(total_amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS next_amount,
    -- running total of spend over time
    SUM(total_amount) OVER (
        PARTITION BY customer_id
        ORDER BY created_at
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
WHERE status = 'completed';

CASE Expressions

CASE is SQL's if/else. It is great for bucketing and conditional aggregation (pivoting).

SELECT
    customer_id,
    CASE
        WHEN SUM(total_amount) >= 5000 THEN 'VIP'
        WHEN SUM(total_amount) >= 1000 THEN 'Regular'
        ELSE 'New'
    END AS tier,
    -- conditional aggregation / pivot
    COUNT(*) FILTER (WHERE status = 'completed') AS completed,
    COUNT(*) FILTER (WHERE status = 'refunded')  AS refunded
FROM orders
GROUP BY customer_id;

Date Functions

Analytics is full of time-based questions. Exact syntax varies by engine, but the concepts transfer.

SELECT
    DATE_TRUNC('week', created_at)              AS week,
    EXTRACT(DOW FROM created_at)                AS day_of_week,
    created_at + INTERVAL '30 days'             AS expires_at,
    AGE(NOW(), created_at)                       AS order_age,
    COUNT(*)                                     AS orders
FROM orders
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1, 2, created_at;

Indexes and Performance

Queries that filter or join on unindexed columns force full table scans. An index turns an O(n) scan into an O(log n) lookup.

-- Speed up filtering and joining on customer_id
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- Composite index for a common filter + sort
CREATE INDEX idx_orders_status_date ON orders (status, created_at DESC);

-- Inspect the query plan
EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 42;

Practice Exercises

1. Write a query returning the top 10 customers by total completed spend, including their order count.
2. Find all customers who have never placed an order using a LEFT JOIN anti-join pattern.
3. Using a window function, compute each customer's running total of spend ordered by date.
4. Bucket orders into 'Small', 'Medium', and 'Large' tiers with a CASE expression and count each tier.
5. Write a CTE that computes monthly revenue, then return the 3 months with the highest revenue.
6. For each order, use LAG to compute the number of days since that customer's previous order.
7. Add an index that would speed up a query filtering orders by status and sorting by created_at, then verify it with EXPLAIN ANALYZE.