Ultimate Guide to SQL Patterns in Technical Interviews/

...

/

Group Bucket

Group Bucket

Learn about the bucketing pattern to group data into meaningful categories or ranges.

We'll cover the following...

When analyzing data, we often want to understand how certain values fall into meaningful ranges or categories.

For example, in our online store, what if we want to know how many products are low, medium, or high in stock? Or how many customers belong to each loyalty tier? This kind of grouped insight helps us spot patterns, allocate resources, and make decisions more effectively.

In this lesson, we’ll learn how to group data into buckets, such as predefined value ranges or categories, and then summarize each bucket using aggregate functions like COUNT, SUM, or AVG.

By the end of this lesson, we’ll be able to:

  • Group continuous values into defined ranges (like price or stock levels).

  • Use CASE WHEN, IF, or built-in functions to define bucket logic.

  • Aggregate and summarize results per bucket.

Pattern overview

Category:

  • Aggregation patterns

Intent:

Group continuous or categorical values into logical segments (buckets), and summarize each segment using aggregates.

Motivation:

Raw numbers alone aren’t always meaningful. When we bucket data, we convert raw values into human-friendly segments. This helps answer questions like:

  • How many products are low in stock?

  • How many customers are VIPs vs. regular?

  • What’s the average order value per spending range?

Also known as:

  • Binning

  • Range aggregation

  • Grouping by range

  • Histogram buckets

Structure

This pattern uses CASE, IF, or similar expressions to define custom labels or numeric ranges, and then groups by them. Aggregation functions are then used per group.

SELECT   CASE
          WHEN condition1 THEN 'Bucket 1'
          WHEN condition2 THEN 'Bucket 2'
          ...
          ELSE 'Other'
         END AS BucketLabel,
         AGG_FUNC(column) AS Summary
FROM     Table
GROUP BY BucketLabel;
Syntax for Group bucket pattern

Keywords

  • CASE WHEN

  • IF

  • GROUP BY

  • COUNT, SUM, AVG, etc.

  • Bucketing, binning, categorizing

Problem structure

We use the Group bucket pattern when:

  • We need to group data into defined ranges or categories based on a continuous or categorical column.

  • We define the bucket logic using CASE, IF, or similar conditional expressions.

  • Then, we apply aggregation per bucket using GROUP BY.

  • Look for keywords like: “group by range,” “bucket into,” “age groups,” “categories,” or “segments” to identify when this pattern applies.

Example use cases

1. Group products by stock level.

Given the following structure of the Products table:

Field

Type

ProductID

int

ProductName

varchar(50)

CategoryID

int

Price

decimal(10,2)

Stock

int

LastRestockDate

date

MonthlySales

int

InventoryTurnoverRate

decimal(5,2)

Where ProductID is the primary key and CategoryID is the foreign key from the Categories table. The table contains information about products.

The Products table includes a Stock column that tracks how many units of each product are currently available in inventory. You’ve been asked to categorize products based on their stock levels:

  • 'Low Stock' for stock less than 50

  • 'Medium Stock' for stock between 50 and 150 (inclusive)

  • 'High Stock' for stock greater than 150

Write an SQL query to count how many products fall into each of these stock level categories. Your output should include:

  • StockLevel: The category label based on stock quantity

  • ProductCount: Number of products in that category

Press + to interact
SELECT   CASE
          WHEN Stock < 50 THEN 'Low Stock'
          WHEN Stock BETWEEN 50 AND 150 THEN 'Medium Stock'
          ELSE 'High Stock'
         END AS StockLevel,
         COUNT(*) AS ProductCount
FROM     Products
GROUP BY StockLevel;

2. Group orders into price ranges.

Given the following table structure of Orders table:

Field

Type

OrderID

int

CustomerID

int

OrderDate

date

TotalAmount

decimal(10,2)

ShippedDate

date

DeliveryStatus

enum('Pending','Shipped','Delivered','Cancelled')

EmployeeID

int

CreatedAt

timestamp

ExpectedDeliveryDate

date

ActualDeliveryDate

date

LateDelivery

tinyint(1)

PaymentMethod

enum('Credit Card','PayPal','Cash on Delivery')

ReturnCount

int

FraudRisk

tinyint(1)

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

The Orders table contains a TotalAmount column, which records the total value of each order. You are tasked with analyzing how many orders fall into different value ranges:

  • 'Under $100' for orders less than $100

  • '$100-$300' for orders between $100 and $300 (inclusive)

  • 'Over $300' for orders greater than $300

Write an SQL query to count the number of orders in each value group. The output should include:

  • OrderValueGroup: The value range category

  • OrdersCount: Number of orders in that category

Press + to interact
SELECT   CASE
          WHEN TotalAmount < 100 THEN 'Under $100'
          WHEN TotalAmount BETWEEN 100 AND 300 THEN '$100-$300'
          ELSE 'Over $300'
          END AS OrderValueGroup,
         COUNT(*) AS OrdersCount
FROM     Orders
GROUP BY OrderValueGroup;

3. Count customers by tier.

Given the following structure of the Customers table:

Field

Type

CustomerID

int

CustomerName

varchar(50)

Email

varchar(50)

Phone

varchar(15)

Address

varchar(100)

LastLogin

date

CreatedAt

timestamp

CustomerTier

enum('New','Regular','VIP')

ReferralID

int

LoyaltyPoints

int

LastPurchaseDate

date

IsChurnRisk

tinyint(1)

...