A histogram in SQL: from orders to a customer distribution

Whenever a report shows the average number of orders per customer, I want to look underneath it. The same average can describe a group of similar customers or a mixture of many one-time buyers and a few very active ones. Those situations invite different business decisions, even though the dashboard tile contains the same number.

Here I build an order-count distribution in SQL. I first count each customer’s orders, then customers with a given number of purchases, and finally group the results into readable ranges. Along the way I use a subquery, a CTE and CASE WHEN. The dataset is small enough to check manually without organising an expedition into a data warehouse.

The examples use syntax supported by PostgreSQL. The data is synthetic, and the temporary tables are intended for a separate practice session. I deliberately include a customer without purchases, a cancelled order and an order outside the reporting period. These details determine whether the query answers the question I actually asked.

Defining the question before the query

I want to know how many registered customers placed zero, one, two to five, six to ten, or more than ten completed orders in January 2025. “Registered” matters. Starting exclusively from an order table will not show me people who have placed no orders at all.

For this exercise, the population consists of all six customers in the sample. In a real analysis I would define eligibility: accounts registered before the end of the month, active accounts, a particular country, or another agreed population. Otherwise the denominator can change with the technical contents of a table rather than a business rule.

I use histogram informally because I am building a frequency table for a numerical variable. Exact order counts form a discrete distribution. Grouping them into unequal ranges produces segments that are easiest to present as labelled bars. In a conventional histogram, bin width matters when interpreting bar area, as the NIST explanation describes.

I would not stretch an open-ended “11+” category across a numerical axis with an invented width. SQL will provide its frequency; the visualisation should present it as a clearly named category. The chart should help readers understand the data without requiring a defence at a statistics seminar.

I also decide what “completed” means before writing SQL. Here it is simply the sample’s status value. In a production system, payment, dispatch and delivery may be different events. Choosing one changes the interpretation, so the report needs to state the choice.

Preparing data I can check by hand

I run the following block once in a new session. It creates six customers and 27 orders. Twenty-five orders belong in the analysis, one is cancelled and one is dated February. The order identifier is unique, so one row in orders represents one order.

-- example: fixture
CREATE TEMP TABLE customers (
    customer_id INTEGER PRIMARY KEY
);
CREATE TEMP TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
    ordered_at DATE NOT NULL,
    status TEXT NOT NULL
);
INSERT INTO customers (customer_id) VALUES (1), (2), (3), (4), (5), (6);
INSERT INTO orders (order_id, customer_id, ordered_at, status) VALUES
    (1, 2, '2025-01-03', 'completed'),
    (2, 3, '2025-01-03', 'completed'),
    (3, 3, '2025-01-04', 'completed'),
    (4, 4, '2025-01-03', 'completed'),
    (5, 4, '2025-01-04', 'completed'),
    (6, 4, '2025-01-05', 'completed'),
    (7, 4, '2025-01-06', 'completed'),
    (8, 4, '2025-01-07', 'completed'),
    (9, 5, '2025-01-03', 'completed'),
    (10, 5, '2025-01-04', 'completed'),
    (11, 5, '2025-01-05', 'completed'),
    (12, 5, '2025-01-06', 'completed'),
    (13, 5, '2025-01-07', 'completed'),
    (14, 5, '2025-01-08', 'completed'),
    (15, 6, '2025-01-03', 'completed'),
    (16, 6, '2025-01-04', 'completed'),
    (17, 6, '2025-01-05', 'completed'),
    (18, 6, '2025-01-06', 'completed'),
    (19, 6, '2025-01-07', 'completed'),
    (20, 6, '2025-01-08', 'completed'),
    (21, 6, '2025-01-09', 'completed'),
    (22, 6, '2025-01-10', 'completed'),
    (23, 6, '2025-01-11', 'completed'),
    (24, 6, '2025-01-12', 'completed'),
    (25, 6, '2025-01-13', 'completed'),
    (26, 2, '2025-01-15', 'cancelled'),
    (27, 2, '2025-02-01', 'completed');

Customer 1 has no orders. Customer 2 has one qualifying order despite appearing three times in the order table. The remaining customers have two, five, six and eleven qualifying orders. I write down these expectations before executing the analysis. Otherwise the first successful query can become the definition of a correct result, which is an optimistic testing strategy.

A real source table may have a different grain. If a row represents an item inside an order, COUNT(*) counts lines. If the table stores status history, an order appears repeatedly. Before changing the aggregate function, I inspect the meaning of a row and the keys available to identify it.

The small fixture also separates two kinds of failure. A syntax error stops the query. A wrong assumption about grain can produce a perfectly valid table of incorrect answers. I need checks for both, and a miniature dataset makes the second kind much easier to see.

Counting orders, then counting customers

I begin by deliberately limiting the result to customers with qualifying orders. I filter January before aggregating and keep only completed records. The date range includes its start and excludes its end. This pattern extends cleanly to timestamps without guessing the last possible instant of a month.

-- example: active-customers
SELECT customer_id, COUNT(*) AS order_cnt
FROM orders
WHERE ordered_at >= '2025-01-01'
  AND ordered_at < '2025-02-01'
  AND status = 'completed'
GROUP BY customer_id
ORDER BY customer_id;

The result contains customers 2, 3, 4, 5 and 6, with counts of 1, 2, 5, 6 and 11. A row now represents a customer. I have moved from individual events to a person-level summary. That change is worth pausing over because it explains the rest of the solution.

To find how many customers placed a given number of orders, I need another aggregation. The inner query calculates purchases per person. The outer query counts the resulting rows for each purchase count. A subquery gives me an intermediate result that I can treat as a table.

-- example: subquery
SELECT order_cnt, COUNT(*) AS customer_cnt
FROM (
    SELECT customer_id, COUNT(*) AS order_cnt
    FROM orders
    WHERE ordered_at >= '2025-01-01'
      AND ordered_at < '2025-02-01'
      AND status = 'completed'
    GROUP BY customer_id
) AS orders_per_customer
GROUP BY order_cnt
ORDER BY order_cnt;

Each order count in this tiny sample has one customer. In a larger dataset the frequencies will vary. I count rows rather than sum order_cnt, because the current question concerns customers. Summing would tell me how many orders belong to a group, which is a different measure.

This is a useful checkpoint. The sum of customer_cnt should be five, matching the customers with qualifying purchases. The sum of order_cnt * customer_cnt should be 25. That second reconciliation checks that the distribution preserves the number of relevant orders.

I would inspect the intermediate table before adding more logic. When a complicated result is wrong, the final SELECT is not always the right place to investigate. A clear expectation for each stage makes it easier to find where the meaning changed.

Using a CTE and keeping customers with zero orders

I like CTEs when the query contains stages worth naming. WITH lets me separate the calculation of orders per customer. That improves readability; it does not automatically promise faster execution. The PostgreSQL CTE documentation explains how the engine handles these expressions.

I now start from the customer table. A LEFT JOIN retains a customer even when no order matches. I place the date and status filters inside ON, so they control which orders qualify without removing the customer from the population.

-- example: all-customers
WITH orders_per_customer AS (
    SELECT c.customer_id, COUNT(o.order_id) AS order_cnt
    FROM customers AS c
    LEFT JOIN orders AS o
      ON o.customer_id = c.customer_id
     AND o.ordered_at >= '2025-01-01'
     AND o.ordered_at < '2025-02-01'
     AND o.status = 'completed'
    GROUP BY c.customer_id
)
SELECT customer_id, order_cnt
FROM orders_per_customer
ORDER BY customer_id;

The output contains six rows with order counts 0, 1, 2, 5, 6 and 11. I intentionally use COUNT(o.order_id). It ignores NULL values, so the customer without a matching order receives zero. COUNT(*) would count that customer’s preserved row and return one. A tiny difference in spelling creates a substantial difference in meaning.

If I moved the status condition into WHERE, the row without a matching order would not satisfy the comparison. My zero-order customer would disappear. This is a common way to accidentally restrict a left join to matched records. The PostgreSQL join tutorial explains how unmatched rows receive NULL values on the missing side.

In this model, COUNT(o.order_id) is sufficient because the order key is unique and I am not joining order lines. COUNT(DISTINCT o.order_id) can be useful in some wider joins, but I do not use it as a universal repair kit. Duplicated sales amounts remain duplicated even if I manage to rescue the order count.

I would also make customer eligibility explicit in the real query. Starting from all accounts ever created may include accounts outside the intended business population. Preserving zero-order customers is only useful when they are people the analysis was supposed to cover.

Building ranges with CASE WHEN

Exact counts are useful for validation, but a broad distribution can produce a long table. I therefore assign customers to five categories. Each customer must enter exactly one category, and the conditions must cover every possible value in this model: non-negative integers.

CASE evaluates its conditions in order. After handling zero and one, order_cnt <= 5 captures the remaining values from two to five. The next condition similarly captures six through ten. The CASE documentation describes this selection of the first matching condition.

-- example: buckets
WITH orders_per_customer AS (
    SELECT c.customer_id, COUNT(o.order_id) AS order_cnt
    FROM customers AS c
    LEFT JOIN orders AS o
      ON o.customer_id = c.customer_id
     AND o.ordered_at >= '2025-01-01'
     AND o.ordered_at < '2025-02-01'
     AND o.status = 'completed'
    GROUP BY c.customer_id
), classified AS (
    SELECT customer_id,
        CASE
            WHEN order_cnt = 0 THEN 0
            WHEN order_cnt = 1 THEN 1
            WHEN order_cnt <= 5 THEN 2
            WHEN order_cnt <= 10 THEN 3
            ELSE 4
        END AS bucket_id
    FROM orders_per_customer
), bins (bucket_id, bucket_label) AS (
    VALUES (0, '0'), (1, '1'), (2, '2-5'), (3, '6-10'), (4, '11+')
)
SELECT b.bucket_label, COUNT(c.customer_id) AS customer_cnt
FROM bins AS b
LEFT JOIN classified AS c ON c.bucket_id = b.bucket_id
GROUP BY b.bucket_id, b.bucket_label
ORDER BY b.bucket_id;

A separate bucket identifier solves the label-sorting problem. Text such as 11+ should not appear before 2-5 merely because the computer sorted the strings correctly. I separate the business order from the label a reader sees.

The bins CTE contains the complete category list. Its left join keeps a category visible even when it contains no customers. Grouping only the classified table would omit that category entirely. In a month-by-month report, disappearing categories make comparisons harder and can shift the chart layout.

I again count a nullable field from the joined side: c.customer_id. Counting every row at this stage would make an empty bucket appear to contain a customer. The same left-join counting issue can occur at several levels of one analysis, so fixing it once does not make it disappear forever.

The boundaries are choices for this exercise. In actual work I would base them on the question and the distribution. If nearly everyone falls into 11+, that section needs more detail. If every category contains a single person, the report may be too granular for the sample available.

For recurring reports, I would keep the definitions stable across the comparison period or clearly explain a change. Moving boundaries can alter the number of customers in a category even when nobody changes behaviour. That is a change in measurement, and it deserves to be visible.

Checking results and awkward cases

First I reconcile the customer total: 1 + 1 + 2 + 1 + 1 equals six, matching the input population. I also check that every customer has one category. A missing or repeated assignment sends me back to definitions and joins.

I test the boundaries separately: zero, one, two, five, six, ten and eleven. The fixture already includes most of them. I can create ten by changing customer 6’s qualifying order count. Boundary values deserve particular attention because a misplaced < instead of <= can silently send them elsewhere.

Then I examine extreme cases. With no qualifying orders, all customers should enter the zero bucket. With an empty customer table, all categories should remain visible with zero frequencies. The cancelled order must not affect the result, and the order on the first of February must stay outside January.

For percentages, I choose the denominator deliberately. Two customers in the 2-5 bucket represent 2/6, or about 33.33%, of the full population. Among purchasers only, the same two represent 2/5, or 40%. Both figures are meaningful when labelled. Without the label, the reader has to guess which people the analysis includes.

Rounding can make displayed percentages total slightly more or less than 100%. I do not manually adjust a category simply to produce a tidier table. I retain exact counts for calculations and present percentages using a stated level of precision.

Duplicates, missing identifiers and time boundaries

If an order appears repeatedly, I investigate why: order lines, status history, a repeated import or an actual data error. Only then do I decide how to reduce the data to order grain. Removing identical-looking rows without understanding them can discard valid information.

For a missing customer identifier, I ask whether it represents a guest purchase, an integration issue or incomplete history. The field is mandatory in this fixture. In a real dataset I would not combine every missing identifier into one extraordinarily active customer. I would describe and handle that population separately.

With timestamps, I agree on the business time zone and month boundaries before filtering. An order just after midnight can belong to another calendar date elsewhere. This detail can create recurring differences between teams when they report across time zones.

I also consider late changes. An order may later become cancelled or refunded. A historical report can change if it always uses the latest status. Whether the business wants a current view or a snapshot as known at the time is a reporting decision, and I document it before comparing periods.

Interpreting the distribution and extending the analysis

A large group of one-order customers makes me curious about repeat purchasing. It does not prove weak loyalty. A customer may have registered near the end of the month, bought something needed infrequently, or purchased earlier outside the selected period. I check those possibilities before telling a retention story.

I would compare customers by tenure, acquisition channel and product type, keeping an eye on group size. A useful extension is a fixed observation window after the first purchase. That gives people a more comparable opportunity to place another order, although seasonality and product changes still require attention.

For the report, I place the population, dates, eligible statuses and bucket definitions beside the result. I add one observation and one proposed next step. If I cannot identify a sensible action, I reconsider whether the grouping addresses a real need.

The data may justify investigating a segment without justifying a campaign immediately. I might first review purchasing intervals or compare customers at similar stages of their relationship. A distribution helps me ask a better next question; it rarely contains the entire explanation by itself.

I evaluate performance after correctness is established. With a large dataset I inspect the execution plan, data volume and existing indexes. Rewriting a subquery as a CTE is not a measurement. I also avoid promising that one index will be suitable for every schema and filtering pattern.

Preparing a recurring report

For this one-off exercise, I write dates directly into the query. For a recurring report, I would pass the start and end through the parameter mechanism supported by the reporting tool. Manually replacing the same month in several places creates an avoidable opportunity for different sections to describe different populations.

I would record the refresh date and data-completeness status. A report run on the first day of a month may be missing late-arriving events from the previous period. If the source backfills them later, readers need to know when a result is provisional. A label saying “January” does not establish that all January data has arrived.

When exporting to a visualisation tool, I would retain the bucket identifier alongside its label and frequency. That preserves the intended ordering even if the tool sorts text by default. Zero-frequency categories should travel with the result too. A missing bar can then mean zero customers rather than an unexplained missing row.

For a multi-month report I would build the full set of month-and-bucket combinations, then join the observed counts to it. That extends the same idea used by bins: define what should be visible, then attach the observations. An empty month becomes an understandable result instead of disappearing between neighbouring dates.

Order frequency and customer value answer different questions

A person placing eleven small orders may spend less than somebody placing one large order. I would not turn purchase frequency alone into a ranking of valuable customers. To investigate value, I would separately add revenue, returns and a defined margin at customer grain. Those measures describe different aspects of the relationship.

The zero group also needs careful wording. Zero in January means no qualifying January orders. It does not mean the customer has never bought anything, has left permanently, or has a worthless account. A conclusion about churn needs an inactivity definition appropriate to the product and enough observation time.

I would check how identities are represented. One person using two accounts appears as two customers if that is how the table records them. I would not merge accounts arbitrarily based on similar names. An agreed identity rule is needed, and the report should state whether it measures accounts, people or business entities.

A small exercise in predicting changes

I would begin by adding a seventh customer without orders. I expect two customers in the zero bucket and seven in the population. The number of completed January orders stays at 25. If anything else changes, I have a specific signal to investigate.

Next I would move one of customer 6’s orders outside the reporting month. Their count drops from eleven to ten, so they move from 11+ to 6-10. The customer total remains seven, qualifying orders fall to 24, and the final bucket remains visible with a zero. One data change now has three independent expectations.

Finally I would remove every order from the practice fixture while keeping the customers. All seven should enter the zero bucket. That short exercise covers population logic, filtering, aggregation and empty categories. If I can predict the result and explain each difference, I understand the mechanism. Adding well-defined business KPIs then helps connect the frequency table to a question that matters to its reader.