Creating Flags & Buckets — Create flags and buckets from numeric or date fields - SQL

SECTION 1

Historical Context & Motivation

Long before SQL existed, analysts and statisticians faced a fundamental challenge: continuous numeric data—revenue figures, timestamps, sensor readings—is difficult to summarize, compare, and communicate in its raw form. The practice of discretization, or converting continuous values into discrete categories, has roots in early statistical analysis where researchers binned observations into frequency tables and histograms. As relational databases matured throughout the 1980s and 1990s, the need to perform this categorization inside the database engine—rather than exporting data and manipulating it in application code—became critical for performance and consistency. Two dominant patterns emerged: flags (binary indicators) and buckets (multi-level categories), both expressible through standard SQL constructs.

1970 • Codd's Relational Model

Edgar F. Codd publishes the relational model, establishing that data should be stored in tables with typed columns—numeric, date, and character—laying the groundwork for in-database transformations.

1986 • SQL-86 Standard

The first ANSI SQL standard formalizes basic query syntax, but lacks conditional expressions like CASE, forcing developers to use application-layer logic for flagging and bucketing.

1992 • SQL-92 & the CASE Expression

SQL-92 introduces the CASE expression, providing a portable, declarative way to create flags and buckets directly in SELECT statements. This is the cornerstone construct for in-query data transformation.

2003 • SQL:2003 & WIDTH_BUCKET

The SQL:2003 standard introduces the WIDTH_BUCKET function, enabling equal-width histogram bucketing as a first-class SQL function—significantly simplifying numeric discretization.

2010s • Analytics & Feature Engineering Era

Modern analytics warehouses (BigQuery, Snowflake, Redshift) make flags and buckets essential for dashboards, cohort analysis, and machine-learning feature pipelines executed entirely in SQL.

The central question this lesson addresses is straightforward yet powerful: given a table with numeric columns (prices, scores, counts) or date columns (order dates, signup timestamps), how do we write SQL that produces meaningful categorical labels—binary flags like is_high_value or multi-level buckets like age_group—directly within a query, without leaving the database?

SECTION 2

Core Principles & Definitions

Before diving into syntax, it is important to establish a precise vocabulary for the two transformation patterns. A flag is a binary (0/1 or TRUE/FALSE) derived column that answers a yes-or-no question about each row. A bucket (also called a bin or tier) is a derived column that maps a continuous value into one of several mutually exclusive, collectively exhaustive categories. Both constructs share a common structural principle: they use conditional expressions to evaluate each row and assign a label. The distinction is cardinality: flags yield exactly two outcomes, while buckets yield three or more.

Flags (Binary Indicators)

A flag answers a single Boolean question per row. Typically implemented with CASE WHEN ... THEN 1 ELSE 0 END or the shorter IIF() in some dialects. Output cardinality: exactly 2.

Buckets (Multi-Level Bins)

Buckets partition a continuous domain into N ≥ 3 labeled segments. A searched CASE expression evaluates conditions top-to-bottom, assigning the first matching label. Alternatively, WIDTH_BUCKET() automates equal-width binning.

Numeric vs. Date Fields

Numeric fields (INT, DECIMAL, FLOAT) use arithmetic comparisons for thresholds. Date/timestamp fields require date functions such as EXTRACT(), DATEDIFF(), or DATE_TRUNC() to derive a numeric proxy before applying conditions.

MECE Principle

Buckets must be Mutually Exclusive and Collectively Exhaustive (MECE). Every row should fall into exactly one bucket. Always include an ELSE clause or a catch-all condition to handle NULLs and edge cases—otherwise rows silently receive NULL labels.

Evaluation Order

SQL's searched CASE evaluates WHEN clauses sequentially and returns the result of the first TRUE condition. This means the order of conditions is semantically significant; overlapping ranges will silently resolve to the first match.

✦

KEY TAKEAWAY

Think of flags and buckets like sorting mail. A flag is a simple yes/no stamp—"Does this letter go international?"—while a bucket is a set of labeled bins on a sorting shelf: Local, Regional, National, International. In both cases, you inspect a continuous attribute (the destination distance) and assign a discrete label. The SQL CASE expression is your sorting rule sheet.

SECTION 3

Visual Explanation — Flags & Buckets Flow

The diagram shows a source column of continuous order totals flowing through two transformation paths. The top path demonstrates a flag that produces a binary 0 or 1 based on a single threshold (≥ 100). The bottom path shows a bucket that partitions the same values into four labeled tiers using cascading CASE conditions.

The diagram above illustrates the fundamental duality of this lesson. A single source column can produce both a flag and a bucket in the same SELECT statement—these are independent derived columns. Notice how the bucket version requires carefully ordered thresholds: because CASE evaluates top-to-bottom, placing WHEN total < 500 before WHEN total < 200 would incorrectly classify a $150 order as 'High' instead of 'Medium'. This sequential evaluation behavior is the most common source of bucketing bugs in production SQL code.

SECTION 4

SQL Constructs — CASE, IIF, and WIDTH_BUCKET

The Searched CASE Expression

The searched CASE expression is the workhorse for creating both flags and buckets. Unlike the simple CASE form that matches a single expression against literal values, the searched CASE evaluates arbitrary Boolean predicates, giving it the flexibility to handle range-based bucketing and complex flag logic involving multiple columns. Each WHEN clause is evaluated in declaration order; the first clause whose predicate returns TRUE determines the result, and all subsequent clauses are skipped. If no predicate matches, the ELSE clause fires—or NULL is returned if ELSE is omitted.

SEARCHED CASE SYNTAX

CASE WHEN predicate₁ THEN result₁ WHEN predicate₂ THEN result₂ … ELSE default_result END

predicateₙ = any Boolean expression (e.g., amount >= 100); resultₙ = scalar value or expression returned when predicateₙ is the first TRUE match.

Flag Pattern (Binary Output)

FLAG IDIOM

CASE WHEN condition THEN 1 ELSE 0 END AS flag_name

Returns INTEGER 1 when the condition holds, 0 otherwise. Some dialects support IIF(condition, 1, 0) as syntactic sugar (SQL Server, SQLite). PostgreSQL and MySQL allow direct (condition)::INT casting.

Bucket Pattern (Multi-Level Output)

BUCKET IDIOM

CASE WHEN value < t₁ THEN 'Bucket A' WHEN value < t₂ THEN 'Bucket B' … ELSE 'Bucket N' END AS bucket_name

t₁ < t₂ < … < tₙ₋₁ are the boundary thresholds. Because evaluation is sequential and earlier conditions are already FALSE when later ones are reached, each WHEN implicitly represents the range [tₖ₋₁, tₖ).

WIDTH_BUCKET for Equal-Width Binning

WIDTH_BUCKET SYNTAX

WIDTH_BUCKET(expression, min_bound, max_bound, num_buckets)

Returns an INTEGER from 0 to num_buckets + 1. Bucket 0 catches values below min_bound; bucket num_buckets + 1 catches values ≥ max_bound. Each interior bucket k covers the range [min_bound + (k−1) × w, min_bound + k × w) where w = (max_bound − min_bound) ÷ num_buckets.

⚠

NULL Handling

Both CASE and WIDTH_BUCKET return NULL when the input expression is NULL. In flag columns, a NULL is semantically different from 0 (FALSE)—it means "unknown." Always decide upfront whether NULLs should map to 0 (via COALESCE(column, default)) or remain NULL. This choice affects downstream aggregations like SUM(is_high_value) and AVG(is_high_value).

SECTION 5

Date-Based Flags & Buckets

Date and timestamp fields require an additional step compared to plain numeric columns: you must first extract or compute a numeric proxy from the date, then apply the flag or bucket logic to that proxy. Common proxies include the day-of-week (for weekday/weekend flags), the month or quarter (for seasonal buckets), and the days elapsed since a reference date (for recency or tenure buckets). The extract-then-bucket pipeline is a two-stage transformation that composes naturally because SQL allows nesting expressions.

Date-based transformations follow a two-stage pipeline: first extract a numeric proxy (day-of-week, month, days elapsed) from the date field, then apply standard flag or bucket logic to that numeric value. The bottom panel lists dialect-specific extraction functions.

Use Case	Date Proxy	Type	Example Label(s)
Weekend orders	`EXTRACT(DOW FROM order_date)`	Flag	`is_weekend → 0 \| 1`
Fiscal quarter	`EXTRACT(QUARTER FROM order_date)`	Bucket	Q1, Q2, Q3, Q4
Customer tenure	`DATEDIFF(day, signup_date, CURRENT_DATE)`	Bucket	New, Active, Aging, Dormant
Business hours	`EXTRACT(HOUR FROM event_ts)`	Flag	`is_business_hours → 0 \| 1`
Season	`EXTRACT(MONTH FROM order_date)`	Bucket	Winter, Spring, Summer, Fall

Common date-based flag and bucket patterns

SECTION 6

Worked Example — E-Commerce Order Analysis

Consider an orders table with columns order_id INT, customer_id INT, order_total DECIMAL(10,2), order_date DATE, and item_count INT. We want to derive: (1) a flag is_bulk_order for orders with ≥ 10 items, (2) a bucket revenue_tier segmenting order totals, and (3) a date-based bucket season.

Building Flags & Buckets on the orders Table

1
Step 1 — Define the flag predicate

We want a binary indicator for bulk orders. The predicate is straightforward: item_count >= 10. Using the flag idiom: CASE WHEN item_count >= 10 THEN 1 ELSE 0 END AS is_bulk_order. We use ELSE 0 rather than omitting ELSE so that NULLs in item_count produce 0, not NULL—assuming the business rule treats missing counts as non-bulk.

CASE WHEN item_count >= 10 THEN 1 ELSE 0 END AS is_bulk_order
2
Step 2 — Define the revenue bucket thresholds

The business defines four revenue tiers: Micro (< $25), Standard ($25–$99.99), Premium ($100–$499.99), and Enterprise (≥ $500). We write the CASE with ascending thresholds, exploiting sequential evaluation: once order_total < 25 is FALSE, we know order_total >= 25, so the next clause only needs < 100 to represent the range [25, 100).

CASE WHEN order_total < 25 THEN 'Micro' WHEN order_total < 100 THEN 'Standard' WHEN order_total < 500 THEN 'Premium' ELSE 'Enterprise' END AS revenue_tier
3
Step 3 — Extract month and build the season bucket

To derive a season bucket, we first extract the month number using EXTRACT(MONTH FROM order_date). Then we map month ranges to seasons. Note that the month extraction can be nested directly inside the CASE: CASE WHEN EXTRACT(MONTH FROM order_date) IN (12, 1, 2) THEN 'Winter' .... The IN operator keeps the code readable when a bucket corresponds to a set of discrete values rather than a continuous range.

CASE WHEN EXTRACT(MONTH FROM order_date) IN (12,1,2) THEN 'Winter' WHEN EXTRACT(MONTH FROM order_date) IN (3,4,5) THEN 'Spring' WHEN EXTRACT(MONTH FROM order_date) IN (6,7,8) THEN 'Summer' ELSE 'Fall' END AS season
4
Step 4 — Compose the full SELECT statement

We combine all three derived columns alongside the original fields into a single query. Each CASE expression is an independent column alias. The query engine evaluates them independently per row—there is no performance penalty for multiple CASE expressions in a single SELECT, as each is computed in a single pass over the data.

SELECT order_id, customer_id, order_total, order_date, item_count, CASE WHEN item_count >= 10 THEN 1 ELSE 0 END AS is_bulk_order, CASE WHEN order_total < 25 THEN 'Micro' WHEN order_total < 100 THEN 'Standard' WHEN order_total < 500 THEN 'Premium' ELSE 'Enterprise' END AS revenue_tier, CASE WHEN EXTRACT(MONTH FROM order_date) IN (12,1,2) THEN 'Winter' WHEN EXTRACT(MONTH FROM order_date) IN (3,4,5) THEN 'Spring' WHEN EXTRACT(MONTH FROM order_date) IN (6,7,8) THEN 'Summer' ELSE 'Fall' END AS season FROM orders;
5
Step 5 — Verify with aggregate validation

A critical post-step is validating that the buckets are MECE. Wrap the query in a CTE and compute GROUP BY revenue_tier with COUNT(*) and MIN(order_total), MAX(order_total) to confirm each bucket's range boundaries match your intent. Also check for NULL in the bucket column: WHERE revenue_tier IS NULL should return zero rows if your ELSE clause is correct.

WITH tagged AS ( ...full query above... ) SELECT revenue_tier, COUNT(*) AS cnt, MIN(order_total) AS min_val, MAX(order_total) AS max_val FROM tagged GROUP BY revenue_tier ORDER BY min_val;

SECTION 7

CASE vs. WIDTH_BUCKET vs. Application Logic

Multiple strategies exist for creating flags and buckets, each with distinct strengths. The searched CASE expression is the most portable and flexible approach, while WIDTH_BUCKET offers terser syntax for uniform-width binning. Application-layer bucketing (e.g., in Python or Java after querying raw data) provides the greatest programmatic flexibility but pushes more data across the network and duplicates logic across codebases.

Criterion	Searched CASE	WIDTH_BUCKET	Application Layer
Portability	All SQL dialects (SQL-92+)	PostgreSQL, Oracle, DB2; limited in MySQL/SQL Server	Language-dependent, not portable across SQL clients
Custom thresholds	Fully custom, non-uniform widths supported	Equal-width only; custom requires post-mapping	Fully custom
Conciseness	Verbose for many buckets	Single function call	Varies (pandas.cut is terse)
Performance	Computed in-engine, single pass per row	Computed in-engine, potentially optimized internally	Requires transferring raw data; adds latency
Label control	Returns any scalar (string, int, etc.)	Returns integer bucket ID; needs CASE to map to label	Full programmatic control
Composability	Can reference other columns, subqueries, functions	Single numeric expression only	Unlimited

Comparison of bucketing strategies across key dimensions

⚡

WHEN TO USE WHICH

In practice, the searched CASE expression is the default tool because it handles both flags and non-uniform buckets with full label control. Reserve WIDTH_BUCKET for exploratory histogram analysis where equal-width bins are appropriate, and defer to application-layer logic only when the bucketing rules depend on runtime configuration or machine-learning model outputs that change per request.

SECTION 8

Connection to Advanced Techniques

Flags and buckets are foundational building blocks that connect to several advanced SQL and data engineering topics. Understanding these connections helps you recognize when a simple CASE expression evolves into a more powerful construct. Window-based flags combine CASE with window functions to create relative flags—for instance, flagging whether a row's value exceeds the group median. Quantile buckets use NTILE(n) to partition rows into equal-count groups rather than equal-width ranges. Feature engineering in machine-learning pipelines frequently uses bucketed columns as categorical features for models trained on SQL-derived feature tables.

Basic Technique	Advanced Extension	Key Difference
CASE flag (absolute threshold)	CASE + window function (relative threshold)	Threshold is computed per partition (e.g., per customer segment) rather than hardcoded
WIDTH_BUCKET (equal-width)	`NTILE(n) OVER (ORDER BY col)`	NTILE creates equal-count buckets; WIDTH_BUCKET creates equal-width buckets
Inline CASE in SELECT	Materialized/computed columns or views	Persisted flags avoid recomputation; useful when the CASE logic is expensive or frequently reused
Static date buckets	Dynamic cohort analysis with date arithmetic	Cohorts are computed relative to each row's anchor date (e.g., days since first purchase)

From basic flags & buckets to advanced SQL patterns

As you advance, you will encounter scenarios where bucket boundaries are not known at query-authoring time—they may come from a lookup table, a percentile calculation, or even a configuration service. In these cases, joining against a range table (sometimes called a bins table) using inequality joins replaces the CASE expression entirely, yielding a data-driven bucketing strategy that requires no query modifications when thresholds change. This pattern is central to data warehousing methodologies like the Kimball model, where flags and buckets often live as attributes in slowly changing dimension tables.

SECTION 9

Practice Problems

Question 1: CONCEPTUAL

Explain why the order of WHEN clauses in a searched CASE expression matters when constructing buckets. What would happen if you wrote CASE WHEN score < 90 THEN 'B' WHEN score < 60 THEN 'F' ELSE 'A' END to grade students?

Show answer

SQL's searched CASE evaluates WHEN clauses sequentially and returns the result of the first TRUE match. In the given expression, a score of 55 would satisfy score < 90 (TRUE) before ever reaching score < 60, so it would incorrectly receive a 'B' instead of an 'F'. The correct approach is to list the most restrictive (lowest threshold) conditions first: WHEN score < 60 THEN 'F' WHEN score < 90 THEN 'B' ELSE 'A'. This ensures each subsequent clause implicitly represents the next higher range.

Question 2: BASIC CALCULATION

Given a table products(product_id INT, price DECIMAL(8,2)), write a SQL query that adds a flag column is_expensive (1 if price ≥ 100, else 0) and a bucket column price_band with labels: 'Budget' (< $25), 'Mid-range' ($25–$99.99), 'Premium' ($100–$499.99), 'Luxury' (≥ $500).

Show answer

SELECT product_id, price, CASE WHEN price >= 100 THEN 1 ELSE 0 END AS is_expensive, CASE WHEN price < 25 THEN 'Budget' WHEN price < 100 THEN 'Mid-range' WHEN price < 500 THEN 'Premium' ELSE 'Luxury' END AS price_band FROM products;

The flag uses a single threshold (≥ 100). The bucket uses ascending thresholds—since CASE evaluates top-to-bottom, each WHEN implicitly covers the range from the previous boundary to the current one. The ELSE clause catches all prices ≥ 500, ensuring the buckets are collectively exhaustive.

Question 3: INTERMEDIATE

You have a table logins(user_id INT, login_ts TIMESTAMP). Write a query that produces, for each login, (1) a flag is_weekend_login and (2) a bucket time_of_day with values 'Morning' (6–11), 'Afternoon' (12–17), 'Evening' (18–22), 'Night' (23–5). Assume PostgreSQL syntax.

Show answer

SELECT user_id, login_ts, CASE WHEN EXTRACT(DOW FROM login_ts) IN (0, 6) THEN 1 ELSE 0 END AS is_weekend_login, CASE WHEN EXTRACT(HOUR FROM login_ts) BETWEEN 6 AND 11 THEN 'Morning' WHEN EXTRACT(HOUR FROM login_ts) BETWEEN 12 AND 17 THEN 'Afternoon' WHEN EXTRACT(HOUR FROM login_ts) BETWEEN 18 AND 22 THEN 'Evening' ELSE 'Night' END AS time_of_day FROM logins;

In PostgreSQL, EXTRACT(DOW ...) returns 0 for Sunday and 6 for Saturday. The hour bucket uses BETWEEN for inclusive ranges; the ELSE catches hours 23 and 0–5. Note that BETWEEN is inclusive on both ends, so hour 11 maps to 'Morning' and hour 12 maps to 'Afternoon' without overlap.

Question 4: APPLIED

A SaaS company stores subscription data in subscriptions(sub_id INT, start_date DATE, monthly_revenue DECIMAL(10,2), status VARCHAR(20)). Write a query that computes the number of days since each subscription started (tenure), creates a tenure bucket ('Trial' ≤ 14 days, 'Onboarding' 15–90, 'Established' 91–365, 'Veteran' > 365), creates a flag is_high_value for monthly_revenue ≥ 500, and then aggregates to show count and average revenue per tenure_bucket and is_high_value combination.

Show answer

WITH tagged AS ( SELECT sub_id, monthly_revenue, CURRENT_DATE - start_date AS tenure_days, CASE WHEN CURRENT_DATE - start_date <= 14 THEN 'Trial' WHEN CURRENT_DATE - start_date <= 90 THEN 'Onboarding' WHEN CURRENT_DATE - start_date <= 365 THEN 'Established' ELSE 'Veteran' END AS tenure_bucket, CASE WHEN monthly_revenue >= 500 THEN 1 ELSE 0 END AS is_high_value FROM subscriptions WHERE status = 'active' ) SELECT tenure_bucket, is_high_value, COUNT(*) AS sub_count, ROUND(AVG(monthly_revenue), 2) AS avg_revenue FROM tagged GROUP BY tenure_bucket, is_high_value ORDER BY tenure_bucket, is_high_value;

This query uses a CTE to first tag every active subscription with its derived columns, then aggregates by the two categorical dimensions. The CTE pattern is essential because SQL does not allow referencing column aliases defined in the same SELECT within a GROUP BY in all dialects. The tenure_days computation uses PostgreSQL's date subtraction, which returns an integer number of days.

Question 5: CRITICAL THINKING

A colleague proposes replacing all CASE-based buckets with WIDTH_BUCKET for simplicity. They also suggest that flags are unnecessary because a bucket with two levels is equivalent. Critically evaluate both claims. Under what conditions is each claim valid, and when does it break down? Provide a concrete example where WIDTH_BUCKET cannot replicate CASE behavior and another where a two-level bucket is semantically different from a flag.

Show answer

Claim 1 (WIDTH_BUCKET for all buckets): WIDTH_BUCKET divides a range [min, max) into N equal-width intervals and returns an integer index. It cannot handle non-uniform bucket widths—for example, an age bucket with intervals [0,18), [18,25), [25,65), [65,∞) has widths of 18, 7, 40, and unbounded, which no single WIDTH_BUCKET call can represent. Furthermore, WIDTH_BUCKET returns an integer, not a descriptive label, so you still need a CASE or lookup to map bucket IDs to business-friendly names. WIDTH_BUCKET is optimal for exploratory equal-width histograms but is not a general replacement. Claim 2 (flags are redundant two-level buckets): Semantically, a flag carries a Boolean connotation—it answers a yes/no question and is typically stored as an integer (0/1) for efficient aggregation via SUM. A two-level bucket returns strings like 'High'/'Low', which cannot be directly summed or averaged. The distinction matters for downstream analytics: SUM(is_high_value) gives the count of high-value rows immediately, whereas SUM(CASE WHEN tier = 'High' THEN 1 ELSE 0 END) requires an extra transformation step. In data warehousing, flags also align with additive fact table measures, while string-valued buckets belong in dimension attributes. The two patterns serve different modeling purposes.

SUMMARY

Lesson Summary

Summary

This lesson introduced two essential SQL data transformation patterns: flags (binary indicators derived from a single Boolean predicate) and buckets (multi-level categories created by partitioning continuous domains into labeled segments). The primary SQL construct for both patterns is the searched CASE expression, which evaluates WHEN clauses in sequential order—making the ordering of conditions semantically critical. For date fields, a two-stage pipeline first extracts a numeric proxy (month, day-of-week, days elapsed) using functions like EXTRACT() or DATEDIFF(), then applies standard CASE logic.

The alternative WIDTH_BUCKET function handles equal-width numeric binning concisely but lacks support for non-uniform thresholds and descriptive labels. All bucket designs should satisfy the MECE principle (mutually exclusive, collectively exhaustive)—ensured by proper threshold ordering and an ELSE clause. Advanced extensions include window-based relative flags, quantile bucketing with NTILE(), and data-driven range-table joins that externalize bucket boundaries for configurability.