Customer segments
- SQL course
- Intermediate
- Lesson 45/115
- 5 min
Write a customer segment report with AVG.
Customer segment
To find the customer segment you compute the average order value with AVG(amount).
SELECT customer_id, AVG(amount) AS avg_order
FROM sales
GROUP BY customer_id;This query gives the average order value per customer.
customer_id, AVG(amount) AS avg_order is selected from sales. Rows are grouped by customer_id; aggregate functions are computed per group.
Customer segment — details
AVG skips NULL values. A high average signals a premium segment.
For example, you can separate customers whose average order is over 500 with HAVING AVG(amount) > 500 and add them to a special campaign list.
Practice Customer segment
Compute the average order value per customer with AVG(amount).
Starter code
-- average order
SELECT customer_id,Sample solution
SELECT customer_id, AVG(amount) AS avg_order
FROM sales
GROUP BY customer_id;Expected output
customer_id avg_order 1 250.50 2 120.00
Questions and answers
Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.
1 Customer segments: match the terms
| Term | Meaning |
|---|---|
AVG |
average |
customer_id |
customer |
GROUP BY |
group |
segment |
segment |
2 How is the average order found?
- SUM
- COUNT
- MIN
- AVG Correct answer
Explanation: AVG gives the average value.
3 Add AVG
SELECT customer_id, ____(amount) AS avg_order
FROM sales
GROUP BY customer_id;SELECT customer_id, AVG(amount) AS avg_order
FROM sales
GROUP BY customer_id;Explanation: AVG(amount) computes each customer's average order amount.