Queries and Joins

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

-- USING collapses the shared column to one output column
SELECT * FROM orders JOIN order_items USING (order_id);

-- Anti-join: customers with no orders
SELECT c.* FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

-- Same thing, usually clearer and often faster
SELECT * FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- Three most recent orders per customer
SELECT c.email, o.id, o.total, o.placed_at
FROM customers c
CROSS JOIN LATERAL (
  SELECT id, total, placed_at
  FROM orders
  WHERE customer_id = c.id
  ORDER BY placed_at DESC
  LIMIT 3
) o;

-- Exactly one row per customer, the latest order
SELECT DISTINCT ON (customer_id) customer_id, id, total, placed_at
FROM orders
ORDER BY customer_id, placed_at DESC, id DESC;

SELECT customer_id, count(*) AS orders, sum(total) AS revenue
FROM orders
WHERE placed_at >= date_trunc('year', now())
GROUP BY customer_id
HAVING sum(total) > 1000
ORDER BY revenue DESC;

SELECT date_trunc('day', placed_at) AS day,
       count(*)                                  AS all_orders,
       count(*) FILTER (WHERE status = 'paid')    AS paid,
       count(*) FILTER (WHERE status = 'cancelled') AS cancelled,
       sum(total) FILTER (WHERE status = 'paid')  AS revenue,
       avg(total) FILTER (WHERE total > 0)        AS avg_nonzero
FROM orders GROUP BY 1 ORDER BY 1;

SELECT status, date_trunc('month', placed_at) AS month, sum(total)
FROM orders
GROUP BY ROLLUP (status, month)
ORDER BY status NULLS LAST, month NULLS LAST;

SELECT status, region, sum(total)
FROM orders GROUP BY GROUPING SETS ((status), (region), ());

SELECT CASE WHEN GROUPING(status) = 1 THEN 'ALL' ELSE status END AS status,
       sum(total)
FROM orders GROUP BY ROLLUP (status);

SELECT id, 'order' AS kind FROM orders
UNION ALL
SELECT id, 'refund' FROM refunds
ORDER BY id DESC
LIMIT 50;

SELECT * FROM orders ORDER BY shipped_at DESC NULLS LAST;
SELECT * FROM orders ORDER BY status, placed_at DESC;
SELECT * FROM orders ORDER BY random() LIMIT 1;   -- fine on small tables only

-- Page 1
SELECT id, placed_at FROM orders
ORDER BY placed_at DESC, id DESC LIMIT 20;

-- Next page, given the last row's (placed_at, id)
SELECT id, placed_at FROM orders
WHERE (placed_at, id) < ($1, $2)
ORDER BY placed_at DESC, id DESC LIMIT 20;

SELECT id,
       coalesce(nickname, name, 'anon')          AS display,
       nullif(discount, 0)                       AS discount,
       greatest(created_at, updated_at)          AS touched,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 100  THEN 'medium'
         ELSE 'small'
       END                                       AS bucket
FROM orders;

SELECT * FROM users WHERE email ILIKE '%@example.com';
SELECT * FROM logs  WHERE message ~ '^(WARN|ERROR)\s';
SELECT regexp_replace('a1b2', '[0-9]', '', 'g');       -- ab
SELECT regexp_match('v1.2.3', '^v(\d+)\.(\d+)');       -- {1,2}
SELECT * FROM regexp_matches('a1b2', '([a-z])(\d)', 'g');
SELECT split_part('a.b.c', '.', 2);                    -- b
SELECT string_to_array('a,b,c', ',');                  -- {a,b,c}