Customer Segmentation in SQL With CASE WHEN

1. Get to one row per customer first

Segmentation is about customers, and the transactions table is about purchases, so the first move is always an aggregate. Nothing about the labelling works until the grain is right.

SELECT customer_id, COUNT(*) AS purchases, SUM(amount) AS spend,
       MAX(purchase_date) AS last_seen
FROM purchases GROUP BY 1;

C2  | 3 | 470 | 2026-03-14
C1  | 3 | 350 | 2026-03-05
C7  | 2 | 300 | 2026-03-19
C6  | 2 | 270 | 2026-03-08
C3  | 2 | 210 | 2026-02-18
C10 | 1 | 190 | 2026-03-04
C4  | 1 | 150 | 2026-01-21
C12 | 1 | 145 | 2026-03-25
C8  | 1 | 130 | 2026-02-17
C11 | 1 |  85 | 2026-03-12
C9  | 1 |  70 | 2026-02-24
C5  | 1 |  60 | 2026-01-28

Twelve rows, and they sum to 2,430, which is the whole purchases table. Those three columns are the classic ingredients of a customer segmentation: how recently, how often, how much. You do not have to use all three, and you do have to compute them before you can band them.

Do this as a named step rather than nesting it, so the customer count can be checked on its own; the argument for that is in subquery against CTE, and it matters here because everything downstream is divided by that count.

2. CASE: the label is just a column

CASE reads a condition list from top to bottom and returns the value attached to the first condition that is true.

CASE WHEN spend >= 300 THEN 'High'
     WHEN spend >= 150 THEN 'Mid'
     ELSE 'Low'
END AS segment

What comes out is an ordinary text column. That is the whole idea and it is the thing worth internalising: the segment is not a special kind of object, it is a value computed per row, which means it can be selected, filtered, joined on, counted and, most usefully, grouped by. The mechanics of the expression itself, including the difference between the simple and searched forms, are in the CASE guide.

Because it is computed rather than stored, the definition lives in the query. That is convenient while exploring and a liability once three reports each contain their own slightly different copy, which is what section eight's reconciliation and the note about materialising it are for.

3. Group by the label

Put the CASE in the select list and group by it, and the segmentation is done.

WITH c AS (SELECT customer_id, SUM(amount) AS spend FROM purchases GROUP BY 1)
SELECT CASE WHEN spend >= 300 THEN 'High'
            WHEN spend >= 150 THEN 'Mid'
            ELSE 'Low' END AS segment,
       COUNT(*) AS customers, SUM(spend) AS spend
FROM c GROUP BY 1;

High | 3 | 1120
Mid  | 4 |  820
Low  | 5 |  490

Three, four and five customers, adding to twelve. Their spend adds to 2,430. Check one by hand: High is C2 at 470, C1 at 350 and C7 at 300, which is 1,120.

And now the finding, which is the point of doing it at all: three customers out of twelve, a quarter of them, account for 1,120 of 2,430, which is 46% of all spending. That is a sentence somebody can act on. "We have twelve customers" is not.

Two syntax notes. GROUP BY 1 means group by the first selected column, which saves repeating the whole CASE; most engines also let you group by the alias. And if yours insists on the full expression in both places, that is the moment to move it into a CTE and group by the resulting column name instead of writing it twice, because two copies drift.

4. The order that deletes a segment

Before the explanation: the same three bands, with the first two conditions swapped. Predict the three counts.

CASE WHEN spend >= 150 THEN 'Mid'
     WHEN spend >= 300 THEN 'High'
     ELSE 'Low' END

Mid | 7 | 1940
Low | 5 |  490

Two rows, not three. High has not shrunk, it has ceased to exist: every customer who would have been High satisfied the 150 test first and was labelled Mid. There is no error, no warning, and no empty High row to notice, because a group with no rows produces no row.

That last part is what makes this dangerous rather than merely wrong. A segment reporting zero would at least be visible on the page. A segment that is simply absent looks like a segmentation with two bands, and the totals still come to twelve customers and 2,430.

CASE takes the first condition that is true, not the best or most specific one, so overlapping conditions are resolved entirely by the order you wrote them in. The rule: most restrictive first, which for numeric bands means starting at the top threshold and working down.

The same stopping rule catches people in every language that has one. If you have met it in a spreadsheet, it is the same mechanism as nested IF against IFS, and the fix is identical.

5. Every row must land somewhere

Before the explanation: here are the bands with no ELSE, and with the middle boundary set to 151 rather than 150. Say what happens to the customer who spent exactly 150.

CASE WHEN spend >= 300 THEN 'High'
     WHEN spend >= 151 THEN 'Mid' END

High | 3 | 1120
Mid  | 3 |  670
NULL | 6 |  640

A third segment appeared, called nothing. CASE with no matching condition and no ELSE returns NULL, and GROUP BY gathers all the nulls into one group, so six customers and 640 of spend are now in a bucket with no name.

Two separate mistakes produced that, and both are worth being able to see independently. The missing ELSE means anything unmatched becomes null instead of landing in a band. The 151 means there is a gap between the bands, so the customer who spent exactly 150 matches neither, which is the boundary problem that every banding scheme has and most banding schemes have not thought about.

Two rules follow, and they are short. Always write an ELSE. And make the bands touch: if one ends at 150 the next begins at 150, expressed as >= 150, so there is nowhere to fall between them.

A useful habit while the segmentation is still being developed: make the ELSE say 'check this' rather than naming a real band. Then anything you did not anticipate arrives labelled as a question rather than quietly joining the bottom group.

6. Two dimensions at once

One CASE per dimension, both in the select list, group by both, and you have a matrix.

frequency | value | customers | spend
often     | High  |         2 |   820
twice     | High  |         1 |   300
twice     | Mid   |         2 |   480
once      | Mid   |         2 |   340
once      | Low   |         5 |   490

Five cells rather than nine, because four of the possible combinations have nobody in them. The customers add to twelve and the spend to 2,430.

Read the shape rather than the individual cells. Everybody who bought once is Mid or Low, and everybody who is High bought at least twice, so on this data value and frequency move together. That is the kind of statement a one-dimensional segmentation cannot make, and it is why the matrix is worth the extra CASE.

Two practical notes. Empty combinations do not appear as zero rows, they simply do not appear, so if the matrix is going into a report, join it to a grid of all combinations first. And resist going to three dimensions: twenty-seven cells over twelve customers is not a segmentation, it is a list with extra steps.

7. Cut-offs you chose, or cut-offs the data chose

The 300 and the 150 came from nowhere. An alternative is to let the distribution decide, with NTILE, which sorts the rows and cuts them into equal-sized groups.

NTILE(3) OVER (ORDER BY spend DESC)

third | customers | lowest | highest | spend
    1 |         4 |    270 |     470 |  1390
    2 |         4 |    145 |     210 |   695
    3 |         4 |     60 |     130 |   345

Four, four and four by construction. Now compare the two methods customer by customer:

customer | spend | fixed | thirds | agree
C2       |   470 | High  | High   | yes
C1       |   350 | High  | High   | yes
C7       |   300 | High  | High   | yes
C6       |   270 | Mid   | High   | no
C3       |   210 | Mid   | Mid    | yes
C10      |   190 | Mid   | Mid    | yes
C4       |   150 | Mid   | Mid    | yes
C12      |   145 | Low   | Mid    | no
C8       |   130 | Low   | Low    | yes
C11      |    85 | Low   | Low    | yes
C9       |    70 | Low   | Low    | yes
C5       |    60 | Low   | Low    | yes

Ten agree and two do not. C6 at 270 is Mid under fixed cut-offs and High under thirds; C12 at 145 is Low under fixed and Mid under thirds. Neither method is wrong, and if those two customers get different treatment, the choice of method is the reason.

Which to use depends on what the segment is for. Fixed cut-offs are right when the number means something outside the data: a spend level at which a discount becomes profitable, a threshold in a contract. They are stable over time and can leave a band empty. Thirds or quartiles are right for relative questions like "who are our best customers", they always fill every band, and they move: a customer can change segment because other customers changed, without buying anything differently.

Whichever you pick, pick it on purpose and write down why. A cut-off chosen because it is round is a decision dressed as an observation, and choosing thresholds from the data is the longer version of that argument.

8. The reconciliation

One query, run every time, and it catches everything on this page.

customers_in | customers_out | spend_in | spend_out
          12 |            12 |     2430 |      2430

Customers going into the segmentation against customers coming out of it, and the same for the money. Equal on both counts means every customer landed in exactly one segment and no spend was created or lost.

Each failure on this page shows up here. A missing ELSE does not change the totals, because nulls still group, but it does add a segment named nothing, so check the segment list as well as the sums. A duplicated customer key inflates customers_out. A join added before the labelling inflates spend_out. And a filter applied inside the segmentation but not to the input makes customers_out smaller, which is the one people write on purpose and then forget.

The related check is the one from section four: every segment you defined should appear in the output. Compare the number of distinct segments returned against the number of labels in your CASE. Fewer means one is unreachable.

Picture your own segmentation for a moment. If a whole tier of it had been unreachable since the day it was written, is there anything on the report that would say so?

Edge cases that break a segmentation

Six worth knowing.

Nulls in the column being banded. NULL >= 300 is unknown, not false, so a customer with no spend falls through every condition to the ELSE and is labelled Low as if they had spent a little. Test for it first: WHEN spend IS NULL THEN 'unknown'.

Customers with no transactions at all. They are not in the purchases table, so they are not in the segmentation. If the question is about all customers, start from the customer table and left join, or the churned ones are invisible.

The window the spend is measured over. A segmentation with no date filter uses all history, so a customer who was big three years ago outranks one who is big now. State the window in the segment name if it matters, as in "spend in the last twelve months".

Segments that move on their own. With NTILE, a customer can drop a tier while spending exactly the same, because others rose. That is correct behaviour and it is a surprise in a monthly report unless the report says so.

Ties at a boundary. NTILE splits by position, not by value, so two customers with identical spend can land in different tiers. If that is unacceptable, band by value instead, or use a ranking function that treats ties together.

The definition living in five places. Once more than one report segments customers, the CASE gets copied and the copies drift. Put it in a view, so the definition has one home, and read temp table against view for the trade that comes with it.