Purchase Frequency Distribution — Solution & ExplanationProblems / Purchase Frequency Distribution / EditorialApproach

Per-customer counts. GROUP BY customer_id + COUNT(*) — the histogram's underlying variable.

Label the ranges. A top-down CASE maps counts to '1', '2-3', '4-5', '6+'. Order of branches is doing real work: each WHEN implicitly carries "and greater than the previous bound".

Count per label, order by the label (these particular labels sort correctly as plain strings; real dashboards often add a numeric sort key column).

Key InsightHistograms over entities are always double aggregations — one pass to compute each entity's value, one pass to count entities per value range. Trying to do it in a single GROUP BY (GROUP BY customer_id, bucket) can't work: the bucket *depends on* the result of the first aggregation. When a metric's input is itself an aggregate, reach for a CTE and stack the passes.Hints

First collapse orders to a per-customer order count; the histogram is an aggregation OF that aggregation.

Because CASE (and F.when chains) evaluate top-down and stop at the first hit, each range bucket only needs its upper bound: = 1, <= 3, <= 5, else 6+.

The final COUNT(*) counts customers, not orders — the first GROUP BY already changed the grain. No DISTINCT needed.

Solve Purchase Frequency Distribution yourself →