SQL for Data Cheatsheet

Query Basics

Select & Filter

SELECT col1, col2 AS alias
FROM table
WHERE col > 10
  AND status IN ('a','b')
  AND name LIKE 'A%'
  AND col BETWEEN 1 AND 5
  AND deleted_at IS NULL
ORDER BY col DESC
LIMIT 20 OFFSET 40;

Clause Order (logical)

FROM -> WHERE -> GROUP BY -> HAVING
  -> SELECT -> DISTINCT -> ORDER BY -> LIMIT

Aggregation

Functions & Grouping

SELECT customer_id,
       COUNT(*)          AS n,
       COUNT(DISTINCT x) AS uniq,
       SUM(amount)       AS total,
       AVG(amount), MIN(amount), MAX(amount)
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

-- WHERE filters rows, HAVING filters groups
-- Conditional / pivot
COUNT(*) FILTER (WHERE status = 'paid') AS paid

Joins

SELECT c.name, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;  -- anti-join

CTEs & Subqueries

WITH t AS (
    SELECT customer_id, SUM(amount) AS spend
    FROM orders GROUP BY customer_id
)
SELECT * FROM t WHERE spend > 1000;

-- scalar subquery
WHERE amount > (SELECT AVG(amount) FROM orders);

-- IN / EXISTS
WHERE id IN (SELECT customer_id FROM orders);
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

Window Functions

ROW_NUMBER() OVER (PARTITION BY g ORDER BY x)
RANK()       OVER (ORDER BY x DESC)
DENSE_RANK() OVER (ORDER BY x DESC)
NTILE(4)     OVER (ORDER BY x)          -- quartiles
LAG(x, 1)    OVER (ORDER BY d)          -- previous row
LEAD(x, 1)   OVER (ORDER BY d)          -- next row
SUM(x) OVER (ORDER BY d
     ROWS BETWEEN UNBOUNDED PRECEDING
     AND CURRENT ROW)                   -- running total
AVG(x) OVER (ORDER BY d
     ROWS BETWEEN 6 PRECEDING
     AND CURRENT ROW)                   -- moving avg

CASE & Dates

CASE WHEN x > 10 THEN 'hi'
     WHEN x > 5  THEN 'mid'
     ELSE 'lo' END AS bucket

DATE_TRUNC('month', ts)
EXTRACT(YEAR FROM ts)
ts + INTERVAL '7 days'
NOW() - INTERVAL '30 days'
COALESCE(x, 0)     -- first non-null
NULLIF(a, b)       -- null if a = b

DDL & Performance

CREATE INDEX idx ON orders (status, created_at);
CREATE UNIQUE INDEX ON users (email);
EXPLAIN ANALYZE SELECT ...;   -- inspect plan

INSERT INTO t (a, b) VALUES (1, 2);
UPDATE t SET a = 1 WHERE id = 5;
DELETE FROM t WHERE id = 5;

-- upsert
INSERT INTO t (id, v) VALUES (1, 9)
ON CONFLICT (id) DO UPDATE SET v = EXCLUDED.v;