SQL Cheat Sheet

SELECT id, name, price
FROM   products
WHERE  price > 100
ORDER BY price DESC
LIMIT  20;

SELECT p.name        AS product,
       p.price * 1.2 AS price_incl_tax
FROM   products AS p;

SELECT DISTINCT country FROM customers;

WHERE age >= 18
  AND status <> 'banned'
  AND country IN ('US','CA','UK')
  AND price BETWEEN 10 AND 50

WHERE email LIKE '%@gmail.com'
  AND name ILIKE 'jo%'       -- case-insensitive (PostgreSQL)
  AND deleted_at IS NULL

SELECT * FROM orders o
WHERE EXISTS (
  SELECT 1 FROM refunds r WHERE r.order_id = o.id
);

SELECT o.id, c.name
FROM   orders o
JOIN   customers c ON c.id = o.customer_id;

FROM orders o
LEFT  JOIN customers c ON c.id = o.customer_id
-- RIGHT JOIN keeps all right rows
-- FULL  JOIN keeps all rows from both

-- managers
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

-- every combination
FROM sizes CROSS JOIN colors;

SELECT region, COUNT(*) AS n, SUM(amount) AS total
FROM   orders
GROUP BY region
HAVING SUM(amount) > 10000
ORDER BY total DESC;

COUNT(*)          COUNT(DISTINCT x)
SUM(x)  AVG(x)
MIN(x)  MAX(x)
STRING_AGG(name, ', ')   -- concat

SELECT
  COUNT(*) FILTER (WHERE status='paid')  AS paid,
  SUM(CASE WHEN refunded THEN amount ELSE 0 END) AS refunded
FROM orders;

SELECT * FROM posts
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

SELECT * FROM posts
ORDER BY created_at DESC
OFFSET 40 ROWS FETCH NEXT 20 ROWS ONLY;

SELECT * FROM posts
WHERE created_at < $last_seen
ORDER BY created_at DESC
LIMIT 20;

WITH top_customers AS (
  SELECT customer_id, SUM(amount) AS spent
  FROM orders GROUP BY customer_id
  HAVING SUM(amount) > 1000
)
SELECT c.name, t.spent
FROM top_customers t
JOIN customers c ON c.id = t.customer_id;

SELECT region, AVG(total) FROM (
  SELECT region, SUM(amount) AS total
  FROM orders GROUP BY region, month
) s
GROUP BY region;

WITH RECURSIVE tree AS (
  SELECT id, parent_id FROM cat WHERE id = 1
  UNION ALL
  SELECT c.id, c.parent_id
  FROM cat c JOIN tree t ON c.parent_id = t.id
)
SELECT * FROM tree;

SELECT name, region, amount,
  ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn,
  RANK()       OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales;

SELECT day, amount,
  SUM(amount) OVER (ORDER BY day) AS running,
  LAG(amount) OVER (ORDER BY day) AS prev_day
FROM daily;

INSERT INTO users (name, email)
VALUES ('Ada', 'ada@x.com'),
       ('Bo',  'bo@x.com');

UPDATE orders SET status = 'shipped'
WHERE id = 42;

DELETE FROM carts
WHERE updated_at < NOW() - INTERVAL '30 days';

INSERT INTO counters (key, n) VALUES ('hits', 1)
ON CONFLICT (key) DO UPDATE
  SET n = counters.n + 1;

CREATE TABLE products (
  id      SERIAL PRIMARY KEY,
  name    VARCHAR(120) NOT NULL,
  price   NUMERIC(10,2) DEFAULT 0,
  created TIMESTAMPTZ DEFAULT NOW()
);

ALTER TABLE products ADD COLUMN sku TEXT;
ALTER TABLE products
  ADD CONSTRAINT uq_sku UNIQUE (sku);

CREATE INDEX idx_orders_cust ON orders (customer_id);
CREATE UNIQUE INDEX uq_email ON users (email);

FOREIGN KEY (customer_id) REFERENCES customers(id)

COALESCE(nickname, name, 'anon')
NULLIF(a, b)         -- NULL when a = b
CASE WHEN x > 0 THEN 'pos' ELSE 'neg' END

UPPER(s)  LOWER(s)  TRIM(s)  LENGTH(s)
CONCAT(a, ' ', b)
SUBSTRING(s FROM 1 FOR 3)
REPLACE(s, 'a', 'b')

NOW()   CURRENT_DATE
DATE_TRUNC('month', ts)          -- PostgreSQL
EXTRACT(YEAR FROM ts)
ts + INTERVAL '7 days'