Aggregates and Group By

SELECT user_id, count(*) AS n
FROM orders
GROUP BY user_id;
-- n per user

SELECT user_id, sum(total) AS spend
FROM orders
GROUP BY user_id
HAVING sum(total) > 1000;
-- big spenders

SELECT count(DISTINCT user_id) FROM orders;
-- unique buyers

SELECT
  count(*) AS all_rows,
  count(*) FILTER (WHERE status = 'open') AS open_rows
FROM orders;
-- all_rows / open_rows

SELECT user_id, string_agg(tag, ',' ORDER BY tag) AS tags
FROM user_tags
GROUP BY user_id;
-- tags: a,b,c

SELECT avg(total), sum(total), min(total), max(total)
FROM orders
WHERE created_at >= CURRENT_DATE - 30;
-- last 30 days stats

SELECT date_trunc('day', created_at) AS day, count(*)
FROM events
GROUP BY 1
ORDER BY 1;
-- daily counts

SELECT region, product, sum(sales)
FROM t
GROUP BY GROUPING SETS ((region), (product), ());
-- region totals, product totals, grand total

SELECT year, month, sum(n)
FROM sales
GROUP BY ROLLUP (year, month);
-- month, year, grand

SELECT user_id, bool_and(ok) AS all_ok
FROM checks
GROUP BY user_id;
-- all_ok true if every check passed

SELECT user_id, array_agg(order_id ORDER BY created_at) AS order_ids
FROM orders
GROUP BY user_id;
-- order_ids: {1,2,3}

SELECT percentile_cont(0.5) WITHIN GROUP (ORDER BY latency_ms)
FROM requests;
-- median latency

SELECT count(*), count(email) FROM users;
-- second ignores null emails

SELECT user_id, max(created_at) AS last_seen
FROM events
GROUP BY user_id;

SELECT user_id, count(*)
FROM orders
WHERE status = 'paid'
GROUP BY user_id
HAVING count(*) >= 3;
-- repeat paid buyers