Aggregations and GROUP BY

Analytics is mostly summarizing: counts, sums, averages — per segment.

Aggregate functions

SELECT
  count(*)        AS orders,
  sum(amount)     AS revenue,
  avg(amount)     AS avg_order_value,
  max(created_at) AS latest_order
FROM orders;

Per-group summaries

GROUP BY splits rows into groups and aggregates each one:

SELECT
  country,
  count(*)    AS orders,
  sum(amount) AS revenue
FROM orders
GROUP BY country
ORDER BY revenue DESC;

Filtering groups with HAVING

WHERE filters rows before grouping; HAVING filters groups after:

SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
HAVING count(*) >= 3;

Exercise: which three countries have the highest average order value, counting only shipped orders?