Group, filter groups, and average at the right level

short · 40 min · Objective 2.1

Task

Summarise units sold by category, keep only the categories above a threshold with HAVING, and show that the average per customer and the average per order are different questions with very different answers.

Steps

  1. Sum quantity by product category, joining orders to products, and save the result to lab/agg/by-category.csv with header category,units.
  2. Repeat with HAVING SUM(quantity) > 1150 and save it to lab/agg/over-1150.csv with the same header.
  3. Calculate the average quantity per order, and the average total quantity per customer using a common table expression, and record them in lab/agg/averages.txt as per order: N and per customer: N, each to two decimal places.

Verify

These checks run in a POSIX shell with Python 3: Terminal on macOS or Linux, and on Windows Git Bash (it comes with Git for Windows) or WSL. On Windows, python3 is often a Microsoft Store placeholder that prints “Python was not found”; run alias python3=py in Git Bash first.

awk -F, 'NR>1 {n++; s+=$2} END {print n" categories, "s" units"}' lab/agg/by-category.csv
awk -F, 'NR>1 {print $1}' lab/agg/over-1150.csv
grep -E '^per (order|customer): ' lab/agg/averages.txt

Five categories and 5690 units in all; three -- Kitchen, Toys and Electronics -- sell more than 1150. The average order is 2.85 units and the average customer bought 18.97: both are "the average quantity", and they answer different questions.

This is an independent study companion for CompTIA Data+ DA0-002 and is not produced by or endorsed by CompTIA.