SQL cheat sheet

SELECT    -- 5. choose columns
FROM      -- 1. pick tables
JOIN      -- 2. combine
WHERE     -- 3. filter rows
GROUP BY  -- 4. group
HAVING    -- 6. filter groups
ORDER BY  -- 7. sort
LIMIT     -- 8. cut

WHERE amount BETWEEN 10 AND 100
  AND region IN ('North', 'West')
  AND name LIKE 'A%'          -- starts with A
  AND email IS NOT NULL
  AND NOT (status = 'test')

SELECT region,
       COUNT(*)                 AS rows,
       COUNT(DISTINCT customer) AS customers,
       SUM(amount)              AS revenue,
       AVG(amount)              AS avg_order
FROM orders
GROUP BY region
HAVING SUM(amount) > 1000;

FROM orders o
JOIN customers c        ON c.id = o.customer_id  -- matches only
LEFT JOIN refunds r     ON r.order_id = o.id     -- keep all orders
FULL OUTER JOIN x       ON ...                   -- keep both sides
CROSS JOIN dim_date d                            -- every combination

SELECT c.*
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.id
);

WITH monthly AS (
  SELECT DATE_TRUNC('month', order_date) AS m,
         SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT * FROM monthly WHERE revenue > 5000;

ROW_NUMBER() OVER (PARTITION BY customer_id
                   ORDER BY order_date DESC)
RANK()       OVER (ORDER BY revenue DESC)
DENSE_RANK() OVER (ORDER BY revenue DESC)
NTILE(4)     OVER (ORDER BY revenue)  -- quartiles

SUM(amount) OVER (ORDER BY d
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AVG(amount) OVER (ORDER BY d
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
LAG(revenue)  OVER (ORDER BY month)
LEAD(revenue) OVER (ORDER BY month)

CASE
  WHEN amount >= 1000 THEN 'Large'
  WHEN amount >= 100  THEN 'Medium'
  ELSE 'Small'
END AS order_size

COALESCE(discount, 0)        -- first non-null
NULLIF(orders, 0)            -- 0 becomes NULL
amount / NULLIF(qty, 0)      -- safe divide
WHERE col IS NULL            -- never = NULL

CURRENT_DATE
DATE_TRUNC('month', order_date)
EXTRACT(YEAR FROM order_date)
order_date + INTERVAL '7 days'    -- PostgreSQL
DATEADD(day, 7, order_date)       -- Snowflake
DATEDIFF('day', start_d, end_d)   -- Snowflake

SELECT id FROM a
UNION ALL   -- keep duplicates, fastest
SELECT id FROM b;

UNION       -- remove duplicates
INTERSECT   -- in both
EXCEPT      -- in first, not second

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE
);
INSERT INTO customers (id, name) VALUES (1, 'Ana');
UPDATE customers SET name = 'Anna' WHERE id = 1;
DELETE FROM customers WHERE id = 1;

SELECT email, COUNT(*) AS n
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;