Moving Beyond CASE WHEN for Binning Logic in SQL

In many analytics projects, we often need to bucket numeric metrics into specific ranges. Usually, this starts as a quick CASE WHEN block in SQL. It gets the job done, but as the project grows, that same logic often gets copied into multiple models and reports. When business thresholds change, updating every single instance can become a tedious and error-prone task.

To make this pattern easier to maintain, we can treat these thresholds as data rather than hardcoded SQL. Here is a practical approach.

Note: This pattern is inspired by the insightful discussion on various SQL bucketing methods in “Binning data in SQL” by skeptric (Edward Ross).

The Problem

The analytics projects often accumulate binning logic like this:

case
    when amount < 10 then '0-10'
    when amount < 50 then '10-50'
    when amount < 200 then '50-200'
    else '200+'
end

There is nothing wrong with this query by itself.

The problem is that the same business logic often ends up copied across multiple models, dashboards, notebooks, and reports.

Then a threshold changes.

0-10
10-50
50-200
200+

becomes

0-25
25-80
80-199
199+

Someone now has to find every CASE WHEN statement and update it correctly.

The work is repetitive, difficult to review, and surprisingly easy to get wrong.

A Simpler Approach

Instead of storing the rules inside many CASE WHEN statements, store the thresholds as data.

Step 1: Store Thresholds

For example, the table only stores boundaries:

threshold
---------
0
10
50
200

This can be a small table or a dbt seed.

Once the thresholds exist as data, the bin definitions & labels can be generated automatically.

Step 2: Generate Bin Definitions

Using a window function lead:

create view bins AS
select
    threshold as bin_start,
    lead(threshold) over (order by threshold) as bin_end,
    ... as label
from thresholds

The SQL for this step rarely changes.

Once you choose an interval convention, it can be reused for many different binning problems.

Note: Keeping these generated bins as a View is highly recommended, as it ensures any changes to thresholds are instantly computed and ready for inspection.

| bin_start | bin_end | label  |
| --------- | ------- | ------ |
| 0         | 10      | 0-10   |
| 10        | 50      | 10-50  |
| 50        | 200     | 50-200 |
| 200       | null    | 200+   |

Step 3: Join Where Needed

Instead of repeating CASE WHEN logic:

select
    o.*,
    b.label
from orders o
left join bins b
    on (
        b.bin_start is null
        and o.amount < b.bin_end
    )
    or (
        b.bin_start is not null
        and o.amount >= b.bin_start
        and (
            o.amount < b.bin_end
            or b.bin_end is null
        )
    )

Now threshold changes only require updating a small configuration table.

The join logic stays the same.

Why We Need This Pattern

A few things become easier:

• Thresholds are visible in one place
• Changes are easier to review in Git
• Labels stay consistent
• The same logic can be reused across models
• There is less SQL to maintain

Most importantly, this shifts your workflow from hardcoding business rules in SQL to managing configuration as data.

Turning It Into a dbt Package

If you use dbt for your analytics data pipeline, I packaged it into a small dbt package called **dbt-binning**, it:

• Store thresholds as data
• Generate bins automatically
• Reduce repetitive CASE WHEN / JOIN maintenance

How to Use the dbt-binning

Generate bins

-- models/amount_bins.sql
{{ generate_bins(ref('amount_thresholds')) }}

Join from another model

The package provides a bin_join macro to handles the join condition automatically:

select
    orders.order_id,
    orders.amount,
    amount_bins.label as amount_bin
from orders
{{ bin_join(
    value='orders.amount',
    bins=ref('amount_bins')
) }}

Summary

By shifting binning rules from hardcoded SQL blocks into configuration tables, we can keep our definitions consistent and much easier to update in one place.

The next time a threshold changes, it only requires a quick update to a seed row rather than scanning through multiple SQL files.

Would love to hear how you handle this pattern.