Analytics is mostly summarizing: counts, sums, averages — per segment.
SELECT
count(*) AS orders,
sum(amount) AS revenue,
avg(amount) AS avg_order_value,
max(created_at) AS latest_order
FROM orders;
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;
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?
Sign in to track your progress.
0 of 3 done
Question 1
You want the customers with at least three orders. Which clause completes the query?
SELECT customer_id, count(*) AS orders
FROM orders
GROUP BY customer_id
____ count(*) >= 3;
Question 2
What does count(*) count in a grouped query?
Question 3
What does this query return?
SELECT avg(amount)
FROM (VALUES (10), (20), (60)) AS t(amount);
Your answers aren't saved. Sign in to keep your progress.