Lesson and practical work
Professional outcomes
- Use COUNT, SUM, AVG, MIN, and MAX at an intentional grain.
- Choose denominators that match the business question.
- Recognize how NULL, duplicates, and one-to-many joins change metrics.
- Interpret aggregate movement using volume, rate, and mix.
Need a refresher?Aggregation compresses rows into measuresView concise foundation
COUNT(*) counts rows; COUNT(column) counts non-NULL values; COUNT(DISTINCT key) counts unique non-NULL keys. SUM and AVG are meaningful only when the values and grain match the metric definition.
Calculate monthly sales, orders, customers, and order value
The sales team needs completed-order net revenue, order count, purchasing customers, and average order value. These measures share a population but not necessarily the same denominator.
Calculate a coherent metric set
SELECT
COUNT(*) AS completed_orders,
COUNT(DISTINCT customer_id) AS purchasing_customers,
SUM(net_revenue) AS net_revenue,
AVG(net_revenue) AS average_order_value,
SUM(net_revenue) / NULLIF(COUNT(DISTINCT customer_id), 0) AS revenue_per_customer
FROM orders
WHERE status = 'completed'
AND order_date >= '2026-07-01'
AND order_date < '2026-08-01';AOV uses orders as its conceptual denominator; revenue per customer uses distinct purchasing customers. Both averages can move in different directions and answer different questions.
What the result means—and does not mean
A higher average does not show that every customer improved. It may reflect a changed distribution or mix. Report the underlying volume and consider median or percentile measures when extremes matter.
Analyst's checklist
- Validate row grain before SUM or AVG.
- Compare COUNT(*) and distinct order_id.
- Inspect NULL and zero revenue separately.
- Confirm cancelled, returned, and test orders.
- Reconcile aggregate revenue to a trusted total.
Common mistakes and failure modes
- Using AVG on order-line revenue and calling it AOV.
- Counting rows after a one-to-many join as orders.
- Ignoring NULL behavior in COUNT(column).
- Comparing averages without population size.
Challenge yourself
After joining orders to order_items, COUNT(*) rises from 10,000 to 34,000 and AVG(order_total) changes.
- Which count still represents orders?
- Why is average order value distorted?
- What aggregation strategy preserves both product and order analysis?
Show hint
The result is now at line-item grain.
Show analytical guidance
Use COUNT(DISTINCT order_id) for order count, aggregate line values to order grain before calculating AOV, or compute order metrics in a separate CTE before joining product detail.
How this may appear in an interview
What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?
What a strong candidate should communicate
Explain row count, non-NULL count, and unique non-NULL count, then connect the choice to grain and business meaning. Mention the performance and interpretation trade-offs where relevant.
Would COUNT(DISTINCT customer_id) measure active customers accurately?
Where this skill becomes evidence
Include a metric dictionary showing numerator, denominator, population, grain, exclusions, and validation for every headline measure.
Starter SQL
SELECT
COUNT(*) AS orders,
SUM(net_revenue) AS revenue
FROM orders
WHERE status = 'completed';Test your analytical judgment
Answer before opening the guidance. More than one defensible approach may exist.
Why do COUNT(*) and COUNT(customer_id) sometimes differ?
Show guidance
Define AOV and revenue per purchasing customer.
Show guidance
Repair order metrics after a line-item join.
Show guidance
Move from reviewing expert reasoning to performing the analysis.
Guided practice can add the synthetic dataset, multi-step assignment, validation checks, solution comparison, project workflow, and progress tracking.
- Work with the data
- Make and defend assumptions
- Validate the result
- Build portfolio evidence