DEV Community: Baldwin Apps

SQL Pattern Series #21: The Rolling Average Pattern

Baldwin Apps — Sat, 08 Aug 2026 14:25:00 +0000

Some datasets are noisy.

Daily sales fluctuate.
Website traffic spikes.
Sensor readings jump around.

When you look at the raw values, it can be difficult to see what is actually happening.

The Rolling Average Pattern helps smooth out short-term fluctuations so the underlying trend becomes easier to see.

The Pattern

A rolling average uses a window function with a moving frame.

AVG(column) OVER (
    ORDER BY date
    ROWS BETWEEN n PRECEDING
    AND CURRENT ROW
)

The frame determines how many previous rows are included in the calculation.

For example:

ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW

creates a 7-row rolling average.

Example

Suppose we want a rolling 7-day average of sales.

SELECT
    date,
    value,

    AVG(value) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING
        AND CURRENT ROW
    ) AS RollingAvg

FROM SalesData;

Instead of showing only today's value, the query calculates the average across the current row and the previous six rows.

This smooths out day-to-day fluctuations.

Intermediate Note

The frame definition is important.

ROWS BETWEEN 6 PRECEDING
AND CURRENT ROW

means:

Current row
Previous 6 rows

for a total of 7 rows.

Changing the frame changes the smoothing effect.

Examples:

ROWS BETWEEN 2 PRECEDING
AND CURRENT ROW

3-row rolling average

ROWS BETWEEN 29 PRECEDING
AND CURRENT ROW

30-row rolling average

Larger windows create smoother trends but react more slowly to changes.

Why This Pattern Matters

Raw data often contains noise.

Rolling averages help reveal the signal hidden underneath.

Common uses include:

Sales trends
Revenue analysis
Website traffic monitoring
Sensor measurements
Financial data
Operational metrics
Capacity planning

The goal is not to replace the raw data.

The goal is to make trends easier to see.

Thinking in Windows

A useful mental model is to imagine a small window moving down the result set.

At each row:

Collect the rows inside the window.
Calculate the average.
Move the window forward one row.
Repeat.

The average changes gradually because most of the rows in the window remain the same from one calculation to the next.

SQL Dialect Note

Rolling averages are supported by modern versions of:

SQL Server
PostgreSQL
MySQL 8+
MariaDB
Oracle

Window functions are one of the most powerful features in modern SQL and are widely used in reporting and analytics workloads.

When I Reach for This Pattern

I reach for this pattern whenever someone says:

"The numbers are all over the place."

A rolling average often reveals the trend that the raw data is hiding.

Key Takeaway

The Rolling Average Pattern smooths short-term fluctuations so long-term trends become easier to understand.

Sometimes the most important story in the data isn't today's value.

It's the direction things have been moving over time.

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

Download on iOS:
https://apps.apple.com/us/app/sql-bubble-pop-sql-coding-game/id6744767120
Learn more:
https://sqlbubblepop.com

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #20: The Filtered Aggregate Pattern

Baldwin Apps — Tue, 04 Aug 2026 14:27:00 +0000

SQL Pattern Series #20: The Filtered Aggregate Pattern

Sometimes you need multiple metrics from the same dataset.

For example:

total orders
completed orders
cancelled orders
completed revenue

A common beginner approach is writing a separate query for each metric.

The Filtered Aggregate Pattern lets you calculate them all in a single query.

The Pattern

Use an aggregate function together with a CASE expression.

SUM(
    CASE
        WHEN condition
        THEN value
        ELSE 0
    END
)

COUNT(
    CASE
        WHEN condition
        THEN 1
    END
)

Common aggregate functions include:

SUM()
COUNT()
AVG()
MIN()
MAX()

The idea is simple:

Aggregate only the rows you care about.

Example

Suppose we want several metrics for each customer.

SELECT
    customer_id,

    COUNT(*) AS total_orders,

    COUNT(
        CASE
            WHEN status = 'Completed'
            THEN 1
        END
    ) AS completed_orders,

    SUM(
        CASE
            WHEN status = 'Completed'
            THEN amount
            ELSE 0
        END
    ) AS completed_revenue,

    COUNT(
        CASE
            WHEN status = 'Cancelled'
            THEN 1
        END
    ) AS cancelled_orders

FROM Orders
GROUP BY customer_id;

This query returns multiple business metrics from a single grouped result set.

Intermediate Note

When using COUNT(CASE...), many SQL developers omit the ELSE clause entirely.

Because COUNT() ignores NULL, this naturally counts only rows that satisfy the condition.

For example:

COUNT(
    CASE
        WHEN status = 'Completed'
        THEN 1
    END
)

is equivalent to:

COUNT(
    CASE
        WHEN status = 'Completed'
        THEN 1
        ELSE NULL
    END
)

This is why you'll often see:

COUNT(CASE WHEN status = 'Completed' THEN 1 END)

in production SQL code.

Why This Pattern Matters

The Filtered Aggregate Pattern is one of the most practical reporting techniques in SQL.

It allows you to calculate multiple KPIs without repeatedly scanning the same table.

Common uses include:

Completed vs cancelled orders
Paid vs unpaid invoices
Active vs inactive users
Revenue by status
Success vs failure counts
Dashboard metrics

Instead of writing multiple queries, you can often calculate everything in a single grouped query.

Thinking in Buckets

A useful mental model is to think of each CASE expression as a bucket.

CASE
    WHEN status = 'Completed'
    THEN amount
    ELSE 0
END

places revenue into the "Completed" bucket.

The aggregate function then totals everything in that bucket.

This makes complex reports much easier to reason about.

SQL Dialect Note

The Filtered Aggregate Pattern works across major SQL platforms including:

SQL Server
PostgreSQL
MySQL
MariaDB
Oracle

Some databases also support additional syntax such as FILTER(), but the CASE approach is widely portable.

When I Reach for This Pattern

I reach for this pattern whenever someone asks:

"Can we see multiple metrics in the same report?"

Instead of running separate queries, a filtered aggregate often produces everything needed in a single result set.

Key Takeaway

The Filtered Aggregate Pattern allows you to calculate multiple metrics from the same dataset by combining aggregate functions with conditional logic.

The fastest query is often the one that answers multiple questions in a single pass through the data

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

Download on iOS:
https://apps.apple.com/us/app/sql-bubble-pop-sql-coding-game/id6744767120
Learn more:
https://sqlbubblepop.com

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #19: The Reset Pattern

Baldwin Apps — Sat, 01 Aug 2026 14:37:00 +0000

Choosing between surgical removal and a full reset

SQL Pattern Series #19 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

The difference between DELETE and TRUNCATE
When each approach is appropriate
Why intent matters when removing data
Common scenarios for each operation

Most developers eventually need to remove data.

The question is:

Do you want to remove some rows?

or:

Do you want to remove everything?

The answer determines which tool you reach for.

The Problem

Imagine an Orders table.

Sometimes you need to remove:

old records
test data
invalid transactions
duplicate imports

Other times you simply want to start over.

Those are two very different goals.

The Reset Pattern

The Reset Pattern is about choosing the appropriate level of removal.

There are two common approaches:

Surgical Reset

Remove only the rows you specify.

DELETE

Nuclear Reset

Remove every row in the table.

TRUNCATE TABLE

Both remove data.

But they communicate very different intent.

Using DELETE

A DELETE statement removes rows selectively.

For example:

DELETE
FROM Orders
WHERE OrderDate < '2024-01-01';

This query removes only older orders.

Everything else remains.

Conceptually, SQL asks:

Which rows match the condition?

Only those rows are removed.

Using TRUNCATE

A TRUNCATE TABLE statement removes all rows.

For example:

TRUNCATE TABLE Orders;

This operation clears the table entirely.

Conceptually, SQL asks:

Do you want to keep any rows?

If the answer is no, the table is emptied.

Why This Pattern Matters

Many SQL mistakes occur when developers choose the wrong level of removal.

Examples include:

deleting too much data
deleting too little data
writing unnecessary filters
performing large row-by-row deletions when a full reset is intended

The Reset Pattern encourages you to think about intent first.

Surgical vs Nuclear

DELETE

Best when:

specific rows must be removed
conditions are important
only part of the data should disappear

Examples:

old audit records
completed jobs
test users
invalid transactions

TRUNCATE

Best when:

all rows should be removed
a staging table needs to be cleared
test environments need a clean slate
temporary data should be reset

Examples:

ETL staging tables
development environments
test datasets
scratch tables

A Note on Performance

DELETE and TRUNCATE often behave differently internally.

In many database systems:

DELETE removes rows individually
TRUNCATE removes all rows using a more efficient mechanism

As a result, TRUNCATE is often significantly faster when the goal is to empty an entire table.

In some databases, TRUNCATE cannot be rolled back after execution. Always verify transactional behavior before using it in production.

A Note on Database Differences

Support for TRUNCATE, permissions, transaction behavior, logging, and identity reset behavior varies between database systems.

Always consult your database documentation before using it in production.

The pattern remains the same:

DELETE removes selected rows.

TRUNCATE removes all rows.

When I Reach for This Pattern

I typically use DELETE when:

cleaning specific records
removing historical data
fixing data quality issues
applying business rules

I typically use TRUNCATE when:

resetting staging tables
rebuilding test environments
clearing temporary datasets
starting fresh

Key Takeaway

Before removing data, ask:

Am I removing some rows?

or:

Am I removing all rows?

That single question usually points to the correct solution.

Choose the surgical tool when precision matters.

Choose the nuclear option when a full reset is the goal.

SQL Pattern Series

This article is part of the SQL Pattern Series, a collection of practical SQL patterns that help developers recognize common problem-solving approaches found in reporting, analytics, and application development.

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern
SQL Pattern Series #12: The Fallback Pattern
SQL Pattern Series #13: The Gap Detection Pattern
SQL Pattern Series #14: The Top-Per-Group Pattern
SQL Pattern Series #15: The Percent-of-Total Pattern
SQL Pattern Series #16: The Missing Match Pattern
SQL Pattern Series #17: The Reusable Logic Pattern
SQL Pattern Series #18: The Duplicate Detection Pattern

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

Download on iOS:
https://apps.apple.com/us/app/sql-bubble-pop-sql-coding-game/id6744767120
Learn more:
https://sqlbubblepop.com

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #18: The Duplicate Detection Pattern

Baldwin Apps — Tue, 28 Jul 2026 14:25:00 +0000

Finding values that appear more than once

SQL Pattern Series #18 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to identify duplicate values
Why GROUP BY and HAVING work together
When duplicate detection is useful
Why duplicate data can create reporting problems

Many data problems are obvious.

Missing values.

Incorrect values.

Broken relationships.

Duplicate data is different.

Sometimes everything looks normal until the numbers stop making sense.

That is where the Duplicate Detection Pattern becomes useful.

The Problem

Imagine a table of users:

UserID	Email
1	alice@example.com
2	bob@example.com
3	alice@example.com

At first glance, the table appears fine.

But one email address appears twice.

If email addresses are supposed to be unique, that duplicate may indicate:

an import problem
an application bug
bad validation
data quality issues

The challenge is finding those repeated values.

The Duplicate Detection Pattern

A common solution uses:

GROUP BY

combined with:

HAVING

The pattern looks like this:

SELECT
    column_name,
    COUNT(*) AS DuplicateCount
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

The query groups identical values together and returns only groups that appear more than once.

Example

SELECT
    Email,
    COUNT(*) AS DuplicateCount
FROM Users
GROUP BY Email
HAVING COUNT(*) > 1;

This query asks:

Which email addresses occur more than once?

Example Result

Email	DuplicateCount
alice@example.com	2

The result identifies the duplicated value and shows how many times it appears.

Why This Pattern Matters

Duplicate data can quietly distort reports and analytics.

For example:

customer counts become inflated
revenue calculations become inaccurate
dashboards show incorrect totals
automated processes execute multiple times

The earlier duplicates are detected, the easier they are to fix.

GROUP BY vs HAVING

This pattern is a useful reminder of the difference between:

WHERE

and

HAVING

WHERE

Filters rows before grouping.

HAVING

Filters groups after grouping.

For example:

GROUP BY Email
HAVING COUNT(*) > 1

The database must first build the groups before it can determine which groups contain duplicates.

Common Uses

The Duplicate Detection Pattern is frequently used to identify:

Duplicate email addresses

GROUP BY Email
HAVING COUNT(*) > 1

Duplicate usernames

GROUP BY UserName
HAVING COUNT(*) > 1

Duplicate account numbers

GROUP BY AccountNumber
HAVING COUNT(*) > 1

Duplicate product identifiers

GROUP BY ProductCode
HAVING COUNT(*) > 1

Duplicate business keys

GROUP BY BusinessKey
HAVING COUNT(*) > 1

In each case, the goal is the same:

Find values that appear more than once.

Finding the Actual Rows

Sometimes you need more than the duplicated value.

You need the rows themselves.

One common approach is:

SELECT *
FROM Users
WHERE Email IN (
    SELECT Email
    FROM Users
    GROUP BY Email
    HAVING COUNT(*) > 1
);

This returns all rows that participate in a duplicate group.

A Note on Uniqueness Constraints

Ideally, many duplicates should never occur.

Database constraints such as:

UNIQUE

can help prevent duplicate values from being inserted.

However, duplicate detection remains useful when:

auditing existing data
validating imports
cleaning legacy databases
investigating reporting issues

When I Reach for This Pattern

I typically use the Duplicate Detection Pattern when:

validating imported data
troubleshooting reports
checking data quality
preparing data migrations
auditing business keys

Examples include:

duplicate emails
duplicate usernames
duplicate customer records
duplicate account numbers
repeated transaction identifiers

Key Takeaway

Many SQL problems begin with a simple question:

Does this value appear more than once?

The Duplicate Detection Pattern helps answer that question quickly.

Group the values.

Count the occurrences.

Keep the groups greater than one.

Sometimes the problem is not missing data.

Sometimes it is the same data appearing twice.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern
SQL Pattern Series #12: The Fallback Pattern
SQL Pattern Series #13: The Gap Detection Pattern
SQL Pattern Series #14: The Top-Per-Group Pattern
SQL Pattern Series #15: The Percent-of-Total Pattern
SQL Pattern Series #16: The Missing Match Pattern
SQL Pattern Series #17: The Reusable Logic Pattern

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

Download on iOS:
https://apps.apple.com/us/app/sql-bubble-pop-sql-coding-game/id6744767120
Learn more:
https://sqlbubblepop.com

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #17: The Reusable Logic Pattern

Baldwin Apps — Sat, 25 Jul 2026 14:30:00 +0000

Building the logic once so the final query is easier to read

SQL Pattern Series #17 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

What a CTE is
How WITH can make query logic easier to read
Why reusable logic helps reduce complexity
When to use the Reusable Logic Pattern

Some SQL queries become difficult to read because they try to do everything at once.

They calculate values.

Filter rows.

Group data.

Join tables.

Apply business rules.

Return results.

All in one long statement.

That can work.

But it can also become difficult to understand.

That is where the Reusable Logic Pattern becomes useful.

The Problem

Imagine you need to calculate total revenue by customer, then return only customers whose revenue exceeds a threshold.

You could write the logic as a nested subquery.

But as the query grows, the structure becomes harder to follow.

The issue is not always the SQL itself.

Sometimes the issue is that the logic needs a name.

The Reusable Logic Pattern

The Reusable Logic Pattern uses a common table expression, or CTE, to name an intermediate result.

The general form is:

WITH cte_name AS (
    SELECT ...
)
SELECT ...
FROM cte_name;

A CTE lets you build a piece of logic once, give it a meaningful name, and query it in the final statement.

Example

WITH RevenueByCustomer AS (
    SELECT
        CustomerID,
        SUM(Amount) AS TotalRevenue
    FROM Orders
    GROUP BY CustomerID
)
SELECT
    CustomerID,
    TotalRevenue
FROM RevenueByCustomer
WHERE TotalRevenue > 1000;

The first part calculates revenue by customer.

The final query filters the result.

The logic now has a name:

RevenueByCustomer

That name makes the query easier to understand.

Why This Pattern Matters

CTEs can make complex queries easier to read by separating steps.

Instead of forcing the reader to understand everything at once, the query can be read in stages.

For example:

Build revenue by customer.
Query the result.
Apply a final filter.

That structure reduces cognitive load.

It also makes the query easier to debug.

CTEs Are Not Tables

A CTE may look like a temporary table, but it is not necessarily stored as one.

It is a named query expression available to the statement that follows it.

The database optimizer decides how to process it.

That means a CTE is usually best understood as a readability and structure tool, not automatically as a performance tool.

Naming the Logic

The most valuable part of a CTE is often the name.

Compare this:

WITH x AS (
    SELECT ...
)

to this:

WITH RevenueByCustomer AS (
    SELECT ...
)

The second version tells the reader what the intermediate result represents.

Good names help turn query structure into documentation.

Multiple CTEs

You can often use more than one CTE to build a query in steps.

WITH RevenueByCustomer AS (
    SELECT
        CustomerID,
        SUM(Amount) AS TotalRevenue
    FROM Orders
    GROUP BY CustomerID
),
HighValueCustomers AS (
    SELECT
        CustomerID,
        TotalRevenue
    FROM RevenueByCustomer
    WHERE TotalRevenue > 1000
)
SELECT *
FROM HighValueCustomers;

This can make multi-step logic easier to follow.

Each CTE represents one stage of the process.

A Note on Database Differences

CTEs are widely supported, but behavior can vary between database systems.

Some databases may inline CTEs.

Some may materialize them in certain situations.

Some support recursive CTEs.

Some have optimizer rules that affect performance.

The syntax is broadly familiar, but always validate performance and behavior in your specific database system.

When I Reach for This Pattern

I typically use the Reusable Logic Pattern when:

a query has multiple logical steps
nested subqueries are becoming hard to read
intermediate results need meaningful names
the same derived logic is referenced later
I want the query to be easier to debug

Examples include:

revenue by customer
filtered account groups
staged reporting logic
pre-aggregated data
multi-step analytics queries

Key Takeaway

Complex SQL becomes easier when each part has a clear job.

The Reusable Logic Pattern helps answer:

Can I name this piece of logic?

A CTE lets you build the logic once and query it clearly.

Sometimes the best improvement is not a shorter query.

It is a query that explains itself.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern
SQL Pattern Series #12: The Fallback Pattern
SQL Pattern Series #13: The Gap Detection Pattern
SQL Pattern Series #14: The Top-Per-Group Pattern
SQL Pattern Series #15: The Percent-of-Total Pattern
SQL Pattern Series #16: The Missing Match Pattern

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

Download on iOS:
https://apps.apple.com/us/app/sql-bubble-pop-sql-coding-game/id6744767120
Learn more:
https://sqlbubblepop.com

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #16: The Missing Match Pattern

Baldwin Apps — Tue, 21 Jul 2026 07:00:00 +0000

Finding rows that have no related match

SQL Pattern Series #16 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to find rows that do not have related records
Why LEFT JOIN and NULL often appear together
When missing relationships reveal important information
How the Missing Match Pattern differs from the Presence Pattern

Many SQL queries focus on what exists.

Sometimes the more interesting question is:

What is missing?

For example:

Customers who never placed an order
Users who never logged in
Products that have never been sold
Employees without managers
Accounts with no activity

That is where the Missing Match Pattern becomes useful.

The Problem

Imagine two tables:

Customers

CustomerID	CustomerName
1	Alice
2	Bob
3	Charlie

Orders

OrderID	CustomerID
101	1
102	2

Alice and Bob have orders.

Charlie does not.

Suppose you want to find:

Which customers have never placed an order?

The answer is not stored directly.

You must identify the missing relationship.

The Missing Match Pattern

A common solution uses:

LEFT JOIN

combined with a check for:

IS NULL

The pattern is:

LEFT JOIN related_table
WHERE related_table.id IS NULL

This returns rows that failed to find a match.

Example

SELECT
    c.CustomerID,
    c.CustomerName
FROM Customers c
LEFT JOIN Orders o
    ON c.CustomerID = o.CustomerID
WHERE o.CustomerID IS NULL;

The query keeps all customers.

If a matching order exists, the order columns are populated.

If no match exists, the order columns become:

NULL

The final filter keeps only those unmatched rows.

Example Result

CustomerID	CustomerName
3	Charlie

Charlie appears because no matching order was found.

That missing relationship is exactly what we were looking for.

Why This Pattern Matters

Many important business questions involve absence rather than presence.

Examples include:

inactive customers
unsold products
users who never completed onboarding
employees without assignments
projects without owners

The Missing Match Pattern helps surface these gaps.

Presence vs Missing Match

The Presence Pattern asks:

Does a matching row exist?

The Missing Match Pattern asks:

Does a matching row NOT exist?

For example:

Presence Pattern

WHERE EXISTS (...)

Returns rows that have matches.

Missing Match Pattern

LEFT JOIN ...
WHERE related_id IS NULL

Returns rows that do not have matches.

They are closely related patterns that answer opposite questions.

Common Uses

The Missing Match Pattern appears frequently in auditing and reporting.

Customers with no orders

LEFT JOIN Orders
WHERE Orders.CustomerID IS NULL

Products with no sales

LEFT JOIN Sales
WHERE Sales.ProductID IS NULL

Employees without managers

LEFT JOIN Employees m
WHERE m.EmployeeID IS NULL

Users who never logged in

LEFT JOIN LoginHistory
WHERE LoginHistory.UserID IS NULL

In each case, the missing match is the result.

A Note on NOT EXISTS

Many developers also solve this problem using:

NOT EXISTS

For example:

SELECT
    c.CustomerID,
    c.CustomerName
FROM Customers c
WHERE NOT EXISTS (
    SELECT 1
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
);

Many experienced SQL developers prefer NOT EXISTS because it directly communicates the intent of finding rows with no matching related records.

This often expresses the intent very clearly:

Return customers for whom no matching order exists.

Both approaches are common.

The best choice often depends on readability, team conventions, and the specific database system.

When I Reach for This Pattern

I typically use the Missing Match Pattern when:

looking for inactive records
validating data completeness
auditing relationships
identifying orphaned records
finding things that should exist but do not

Examples include:

customers with no orders
products with no sales
users with no activity
projects without owners
records missing related data

Key Takeaway

Many SQL questions are really asking:

What should have a match, but doesn't?

The Missing Match Pattern helps answer that question.

Find the relationship.

Keep the failures.

Sometimes the missing rows are the report.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern
SQL Pattern Series #12: The Fallback Pattern
SQL Pattern Series #13: The Gap Detection Pattern
SQL Pattern Series #14: The Top-Per-Group Pattern
SQL Pattern Series #15: The Percent-of-Total Pattern

SQL Bubble Pop

I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

Download on iOS:
https://apps.apple.com/us/app/sql-bubble-pop-sql-coding-game/id6744767120
Learn more:
https://sqlbubblepop.com

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #15: The Percent-of-Total Pattern

Baldwin Apps — Sat, 18 Jul 2026 14:25:00 +0000

Seeing each value's contribution to the whole

SQL Pattern Series #15 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to calculate percentages without a self-join
How window functions simplify percent-of-total calculations
Why percentages often communicate better than raw numbers
When to use the Percent-of-Total Pattern

Raw numbers are useful.

But sometimes the more important question is:

How much of the total does this represent?

For example:

What percentage of sales comes from each product category?
What percentage of revenue comes from each region?
What percentage of support tickets belong to each team?
What percentage of traffic comes from each source?

That is where the Percent-of-Total Pattern becomes useful.

The Problem

Imagine a sales table:

Category	Sales
Hardware	5000
Software	3000
Services	2000

The total sales are:

But the raw numbers do not immediately show each category's contribution.

We often want to see:

Category	Percent
Hardware	50%
Software	30%
Services	20%

That requires comparing each value to the overall total.

The Percent-of-Total Pattern

A common solution uses a window aggregate:

SUM(column) OVER ()

This calculates the grand total while still preserving individual rows.

The pattern is:

column /
SUM(column) OVER ()

Each row divides itself by the overall total.

Example

SELECT
    Category,
    Sales,
    Sales * 1.0
        / SUM(Sales) OVER ()
        AS PercentOfTotal
FROM SalesData;

This query calculates the total sales once and makes that value available to every row.

Each row then computes its own percentage.

Example Result

Category	Sales	PercentOfTotal
Hardware	5000	0.50
Software	3000	0.30
Services	2000	0.20

To display percentages:

ROUND(
    Sales * 100.0
    / SUM(Sales) OVER (),
    2
) AS PercentOfTotal

Result:

Category	PercentOfTotal
Hardware	50.00
Software	30.00
Services	20.00

Why This Pattern Matters

Humans are often better at understanding proportions than raw counts.

Consider:

Category A: 5,000
Category B: 3,000
Category C: 2,000

versus:

Category A: 50%
Category B: 30%
Category C: 20%

The percentages immediately communicate relative importance.

Window Functions Make This Easy

Before window functions became widely available, developers often solved this problem using:

SELECT
    Category,
    Sales,
    Sales /
    (
        SELECT SUM(Sales)
        FROM SalesData
    )
FROM SalesData;

That approach works.

But window functions often make the intent clearer because both the row value and the total remain visible in the same query.

Percent of Total Within Groups

Sometimes you want percentages inside groups rather than across the entire table.

For example:

What percentage of regional sales comes from each product?

You can partition the total:

SUM(Sales) OVER (
    PARTITION BY Region
)

Now each region receives its own denominator.

The same pattern applies:

Sales /
SUM(Sales) OVER (
    PARTITION BY Region
)

A Note on Integer Division

One common mistake is:

Sales /
SUM(Sales) OVER ()

If both values are integers, some databases may perform integer division.

To avoid this, force decimal math:

Sales * 1.0

or:

CAST(Sales AS DECIMAL(10,2))

The exact syntax varies by database system.

When I Reach for This Pattern

I typically use the Percent-of-Total Pattern when:

comparing contributions
building dashboards
analyzing revenue distribution
measuring category importance
presenting executive summaries

Examples include:

sales by category
revenue by region
tickets by department
users by platform
expenses by cost center

Key Takeaway

Raw numbers tell you how much.

Percentages tell you how important.

The Percent-of-Total Pattern helps answer:

What share of the whole does this represent?

Sometimes contribution matters more than magnitude.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern
SQL Pattern Series #12: The Fallback Pattern
SQL Pattern Series #13: The Gap Detection Pattern
SQL Pattern Series #14: The Top-Per-Group Pattern

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #14: The Top-Per-Group Pattern

Baldwin Apps — Tue, 14 Jul 2026 14:25:00 +0000

Finding the most important row within each group

SQL Pattern Series #14 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to find the top row within each group
Why ROW_NUMBER() is commonly used for this problem
How partitioning creates independent rankings
When to use the Top-Per-Group Pattern

Many SQL problems involve finding a single "best" row.

For example:

The latest order for each customer
The highest sale in each region
The newest status update for each ticket
The most recent login for each user

The challenge is that you don't want the top row overall.

You want the top row within each group.

That is where the Top-Per-Group Pattern becomes useful.

The Problem

Imagine an Orders table:

CustomerID	OrderDate
1	2024-01-15
1	2024-02-10
2	2024-01-20
2	2024-03-01

Suppose you need:

The most recent order for each customer.

A simple ORDER BY won't solve the problem.

You need to rank rows separately within each customer group.

The Top-Per-Group Pattern

The most common solution uses:

ROW_NUMBER() OVER (
    PARTITION BY column
    ORDER BY column DESC
)

The window function creates a ranking inside each group.

The highest-ranked row receives:

rn = 1

Those are the rows we keep.

Example

SELECT *
FROM (
    SELECT
        o.*,
        ROW_NUMBER() OVER (
            PARTITION BY o.CustomerID
            ORDER BY o.OrderDate DESC
        ) AS rn
    FROM Orders o
) t
WHERE rn = 1;

This query:

Groups rows by customer.
Orders each customer's rows from newest to oldest.
Assigns row numbers.
Returns only the first row in each group.

Example Result

CustomerID	OrderDate
1	2024-02-10
2	2024-03-01

Each customer now has exactly one row.

The most recent one.

Why This Pattern Matters

Many reporting and analytics tasks require a single representative row.

Examples include:

latest customer activity
newest support ticket update
highest scoring transaction
most recent account balance
top-performing product per category

The Top-Per-Group Pattern solves these problems consistently.

Partitioning Creates Independent Rankings

The key idea is:

PARTITION BY CustomerID

Without partitioning:

ROW_NUMBER()

would rank all rows together.

With partitioning:

ROW_NUMBER()
OVER (
    PARTITION BY CustomerID
)

each customer receives their own ranking sequence.

Think of it as creating a separate leaderboard for each group.

A Note on Ties

Suppose two orders have the exact same timestamp.

Which row should become:

rn = 1

Without a tie-breaker, the database may choose one arbitrarily.

For deterministic results, consider adding another column:

ROW_NUMBER() OVER (
    PARTITION BY CustomerID
    ORDER BY OrderDate DESC,
             OrderID DESC
)

This guarantees a predictable winner.

ROW_NUMBER vs RANK

The Top-Per-Group Pattern typically uses:

ROW_NUMBER()

because it guarantees exactly one row receives:

rn = 1

Functions such as:

RANK()

or:

DENSE_RANK()

may return multiple rows when ties occur.

That behavior can be useful, but it solves a slightly different problem.

When I Reach for This Pattern

I typically use the Top-Per-Group Pattern when:

I need the latest row per entity
I need the highest value per category
I need one representative row from each group
ranking matters within groups
duplicate historical records exist

Examples include:

latest order per customer
newest employee record
highest sale per region
most recent account activity
latest status change per ticket

Key Takeaway

Many SQL problems are really asking:

Which row wins within each group?

The Top-Per-Group Pattern answers that question.

Create a ranking.

Partition by the group.

Keep:

rn = 1

Sometimes finding the best row is easier than aggregating them all.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern
SQL Pattern Series #12: The Fallback Pattern
SQL Pattern Series #13: The Gap Detection Pattern

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #13: The Gap Detection Pattern

Baldwin Apps — Sat, 11 Jul 2026 14:30:00 +0000

Finding what is missing in a sequence

SQL Pattern Series #13 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

How to detect missing values in a sequence
How LEAD() compares the current row to the next row
Why gaps often reveal important data issues
When to use the Gap Detection Pattern

Sometimes the most important rows are the ones that are not there.

A missing ID.

A missing date.

A missing event.

A skipped invoice number.

A gap in a sequence can reveal that something happened.

Or that something failed to happen.

That is where the Gap Detection Pattern becomes useful.

The Problem

Imagine a table of numbers:

id
1
2
3
7
8

At first glance, the table looks simple.

But something is missing.

The sequence jumps from 3 to 7.

That means 4, 5, and 6 are absent.

A basic query can show the rows that exist.

The Gap Detection Pattern helps you find the spaces between them.

The Gap Detection Pattern

The Gap Detection Pattern compares each row to the next row in sequence.

A common approach uses:

LEAD(column) OVER (
    ORDER BY column
)

LEAD() lets the current row see a value from a following row.

That makes it useful for detecting jumps.

Example

SELECT
    id,
    next_id
FROM (
    SELECT
        id,
        LEAD(id) OVER (
            ORDER BY id
        ) AS next_id
    FROM numbers
) t
WHERE next_id > id + 1;

This query identifies rows where the next value is not the expected next value.

If id = 3 and next_id = 7, then a gap exists.

Example Result

id	next_id
3	7

This result does not list every missing value.

It shows where the gap starts and where the next available value appears.

From this result, you know the missing values are between 3 and 7.

Why This Pattern Matters

Gaps often tell the real story.

They can reveal:

missing records
failed imports
skipped IDs
missing dates
broken event sequences
incomplete logs

In many systems, missing values are not obvious until you compare neighboring rows.

Detecting Date Gaps

The same idea can apply to dates.

For example:

SELECT
    activity_date,
    next_activity_date
FROM (
    SELECT
        activity_date,
        LEAD(activity_date) OVER (
            ORDER BY activity_date
        ) AS next_activity_date
    FROM activity_log
) t
WHERE next_activity_date > activity_date + INTERVAL '1 day';

This checks whether the next date is more than one day after the current date.

Note: Date arithmetic syntax varies by database system. The example above uses PostgreSQL-style interval syntax. SQL Server, MySQL, Oracle, and SQLite use different approaches.

Gap Detection vs Missing Match

Gap detection and missing match are related, but they are not the same pattern.

The Missing Match Pattern asks:

Which rows do not have a related row somewhere else?

The Gap Detection Pattern asks:

Which expected values are missing from a sequence?

Both patterns help reveal absence.

But they look for different kinds of absence.

A Note on Sequences

This pattern works best when there is a meaningful expected order.

For example:

invoice numbers
event numbers
dates
monthly periods
ordered checkpoints

It is less useful when gaps are normal or meaningless.

For example, many database identity columns can have gaps because of rollbacks, deletes, caching, or failed inserts.

A missing ID does not always mean something is wrong.

The question is whether the sequence is meaningful for the business problem.

When I Reach for This Pattern

I typically use the Gap Detection Pattern when:

sequence continuity matters
records should appear in order
missing dates need investigation
logs appear incomplete
expected events did not happen

Examples include:

skipped invoice numbers
missing daily snapshots
missing monthly reports
broken event sequences
incomplete audit logs

Key Takeaway

Existing rows tell you what happened.

Gaps tell you what may be missing.

The Gap Detection Pattern helps answer:

What should be here, but is not?

Sometimes the missing values are the real story.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern
SQL Pattern Series #12: The Fallback Pattern

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #12: The Fallback Pattern

Baldwin Apps — Tue, 07 Jul 2026 14:27:00 +0000

Using the first available value when data is incomplete

SQL Pattern Series #12 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

What COALESCE() does
How fallback values help with missing data
Why fallback logic makes query results easier to use
When to use the Fallback Pattern

Real data is often incomplete.

A customer may not have a phone number.

An employee may not have a work email.

A product may not have a display name.

If the query returns NULL, the result may be technically correct.

But it may not be very useful.

That is where the Fallback Pattern becomes useful.

The Problem

Imagine an Employees table:

EmployeeID	WorkEmail	PersonalEmail
1	alice@company.com	alice@gmail.com
2	NULL	bob@gmail.com
3	NULL	NULL

You want to show one preferred email address for each employee.

The rule is:

Use the work email if it exists.
Otherwise use the personal email.
Otherwise show a default message.

That is fallback logic.

The Fallback Pattern

The Fallback Pattern uses COALESCE() to return the first non-NULL value from a list.

The general form is:

COALESCE(expression1, expression2, ..., expressionN)

SQL evaluates the expressions from left to right.

It returns the first value that is not NULL.

If all values are NULL, the result is NULL unless you provide a final fallback value.

Example

SELECT
    EmployeeID,
    COALESCE(
        WorkEmail,
        PersonalEmail,
        'No Email Provided'
    ) AS PreferredEmail
FROM Employees;

This query says:

Use WorkEmail first.

If WorkEmail is missing, use PersonalEmail.

If both are missing, use 'No Email Provided'.

Example Result

EmployeeID	PreferredEmail
1	alice@company.com
2	bob@gmail.com
3	No Email Provided

The result is easier to read because missing values no longer break the output.

Why This Pattern Matters

NULL is meaningful.

It often means:

This value is unknown, unavailable, or not applicable.

But reports and applications often need something usable to display.

The Fallback Pattern helps you create cleaner output without changing the underlying data.

It is especially useful when data quality varies across sources.

Common Uses

The Fallback Pattern appears in many everyday queries.

Preferred contact value

COALESCE(WorkEmail, PersonalEmail, 'No Email Provided')

Display name fallback

COALESCE(DisplayName, Username, Email)

Address fallback

COALESCE(ShippingAddress, BillingAddress)

Numeric fallback

COALESCE(DiscountAmount, 0)

Each example follows the same idea:

Use the best available value.

A Note on Data Types

Most databases require the expressions inside COALESCE() to be compatible.

For example, this is usually fine:

COALESCE(WorkEmail, PersonalEmail, 'No Email Provided')

because all values are text-like.

But mixing unrelated data types may cause errors or implicit conversions.

As always, check the behavior in your database system.

COALESCE vs ISNULL / IFNULL / NVL

Different database systems may offer vendor-specific functions such as:

ISNULL() in SQL Server
IFNULL() in MySQL and SQLite
NVL() in Oracle

COALESCE() is widely supported and works across many SQL systems.

The exact behavior can vary in small ways, especially around data types, but the pattern is the same:

Return the first available value.

Unlike COALESCE(), these vendor-specific alternatives typically accept only two arguments, so they cannot express a multi-step fallback chain in a single call.

When I Reach for This Pattern

I typically use the Fallback Pattern when:

a display value should not be blank
multiple columns can provide the same kind of value
reports need readable defaults
missing data should not break the result
a query needs a safe substitute for NULL

Examples include:

preferred email addresses
customer display names
default labels
optional discounts
missing category names

Key Takeaway

Missing data does not always have to break the result.

The Fallback Pattern helps answer:

What should I use if this value is missing?

COALESCE() gives SQL a clear fallback chain.

Use the first available value.

Keep the result usable.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern
SQL Pattern Series #11: The Merge Pattern

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #11: The Merge Pattern

Baldwin Apps — Sat, 04 Jul 2026 14:35:00 +0000

Combining result sets without accidentally paying the deduplication tax

SQL Pattern Series #11 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

The difference between UNION and UNION ALL
Why UNION removes duplicates
Why UNION ALL is often faster
When to keep duplicates and when to remove them

Sometimes you need to combine rows from multiple queries.

For example:

Show active customers and archived customers together.

or:

Combine current orders with historical orders.

That is where the Merge Pattern becomes useful.

The Problem

Imagine two result sets:

Set A
-----
1
2
3

Set B
-----
3
4
5

You want one combined result.

But there is an important question:

Should duplicate rows be removed?

That question determines whether you should use UNION or UNION ALL.

The Merge Pattern

The Merge Pattern combines multiple result sets into one result.

The two most common tools are:

UNION

and:

UNION ALL

They look similar.

But they do different work.

UNION Removes Duplicates

UNION combines result sets and removes duplicate rows.

SELECT value
FROM SetA

UNION

SELECT value
FROM SetB;

Result:

value
-----
1
2
3
4
5

The duplicate 3 appears only once.

That can be exactly what you want.

But removing duplicates requires extra work.

UNION ALL Keeps Everything

UNION ALL combines result sets and keeps all rows.

SELECT value
FROM SetA

UNION ALL

SELECT value
FROM SetB;

Result:

value
-----
1
2
3
3
4
5

The duplicate 3 remains.

This is often faster because the database does not need to deduplicate the result.

Why This Pattern Matters

Many developers use UNION by default.

But that default can hide two problems.

First, it may remove rows you actually needed.

Second, it may require unnecessary sorting or deduplication work.

If duplicates are valid, UNION ALL is often the better choice.

The key question is:

Do I need a distinct combined result?

or:

Do I need to preserve every row?

A Practical Example

Suppose you have current and archived orders:

SELECT OrderID, CustomerID, OrderDate
FROM CurrentOrders

UNION ALL

SELECT OrderID, CustomerID, OrderDate
FROM ArchivedOrders;

If the tables represent separate time ranges, duplicates may not be a concern.

In that case, UNION ALL keeps all rows and avoids unnecessary deduplication.

But if the two sources may overlap, and duplicate rows should be removed, then UNION may be appropriate.

Column Rules

Both sides of a UNION or UNION ALL must return compatible columns.

For example:

SELECT CustomerID, CustomerName
FROM ActiveCustomers

UNION ALL

SELECT CustomerID, CustomerName
FROM ArchivedCustomers;

The number of columns should match.

The column positions should represent the same meaning.

The data types should be compatible.

SQL combines by column position, not by column name.

A Note on Performance

UNION often requires extra work because the database must identify duplicate rows.

That may involve sorting, hashing, or other internal operations.

UNION ALL avoids that deduplication step.

So when duplicates are acceptable or impossible, UNION ALL is usually the better default.

As always, validate performance with real execution plans and real workloads.

When I Reach for This Pattern

I typically use the Merge Pattern when:

combining current and historical records
merging active and archived data
stacking results from similar queries
building reporting datasets
combining data from multiple sources

I reach for UNION when:

duplicates must be removed
the final result should be distinct
overlapping sources are possible

I reach for UNION ALL when:

duplicates are valid
all rows should be preserved
the sources do not overlap
performance matters and deduplication is unnecessary

Key Takeaway

UNION and UNION ALL both merge result sets.

But they do not mean the same thing.

UNION says:

Combine the rows and remove duplicates.

UNION ALL says:

Combine the rows and keep everything.

Do not pay the deduplication tax unless you need to.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern
SQL Pattern Series #10: The Hierarchy Pattern

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.

SQL Pattern Series #10: The Hierarchy Pattern

Baldwin Apps — Tue, 30 Jun 2026 14:35:00 +0000

Relating rows in a table to other rows in the same table

SQL Pattern Series #10 of 21

A collection of practical SQL patterns that help developers recognize common solutions to recurring database problems.

What You'll Learn

In this article you'll learn:

What a self-join is
How one table can represent hierarchical relationships
How to connect employees to managers
When to use the Hierarchy Pattern

Most joins connect one table to another table.

For example:

Orders
JOIN Customers

But sometimes the relationship exists inside the same table.

That is where the Hierarchy Pattern becomes useful.

The Problem

Imagine an Employees table:

EmployeeID	EmployeeName	ManagerID
1	Alice	NULL
2	Bob	1
3	Carla	1
4	Diego	2

The employee and the manager are both stored in the same table.

Bob is an employee.

Alice is also an employee.

But Alice is Bob's manager.

So the table needs to relate one row to another row within itself.

The Hierarchy Pattern

The Hierarchy Pattern uses a table more than once in the same query.

This is usually called a self-join.

The key idea is:

Treat the same table as if it were two different roles.

For example:

FROM Employees e
JOIN Employees m
    ON e.ManagerID = m.EmployeeID

Here:

e represents the employee
m represents the manager

Both aliases point to the same table.

But each alias plays a different role in the query.

Example

SELECT
    e.EmployeeID,
    e.EmployeeName,
    m.EmployeeName AS ManagerName
FROM Employees e
JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

This query returns each employee with their manager's name.

The join connects:

e.ManagerID

to:

m.EmployeeID

Conceptually, SQL asks:

Which employee row points to another employee row as its manager?

Example Result

EmployeeID	EmployeeName	ManagerName
2	Bob	Alice
3	Carla	Alice
4	Diego	Bob

Notice that Alice does not appear in this result.

Why?

Because Alice has no manager.

Her ManagerID is NULL, so the inner join does not find a matching manager row.

Keeping Top-Level Rows

If you want to include employees who do not have managers, use a LEFT JOIN:

SELECT
    e.EmployeeID,
    e.EmployeeName,
    m.EmployeeName AS ManagerName
FROM Employees e
LEFT JOIN Employees m
    ON e.ManagerID = m.EmployeeID;

This keeps every employee.

Employees without managers return NULL for ManagerName.

This is useful for top-level roles such as:

CEO
founder
department head
root category

Why This Pattern Matters

Hierarchical relationships appear all over real systems.

Examples include:

employees and managers
categories and parent categories
folders and parent folders
comments and replies
accounts and parent accounts
organization charts

Whenever one row points to another row in the same table, the Hierarchy Pattern may apply.

A Note on Recursion

A self-join is useful for one level of hierarchy.

For example:

employee → manager

But if you need multiple levels:

employee → manager → director → vice president

then a single self-join may not be enough.

For deeper hierarchies, many databases support recursive queries, often using recursive CTEs.

The exact syntax varies by database system.

But the underlying idea is the same:

A row can relate to another row in the same structure.

When I Reach for This Pattern

I typically use the Hierarchy Pattern when:

a table references itself
one row has a parent row
I need to display a parent name
I need to build a basic hierarchy
I need to connect a child row to its immediate parent

Examples include:

employee-manager reports
category trees
folder structures
comment threads
account relationships

Key Takeaway

Not every relationship crosses tables.

Sometimes the relationship lives inside one table.

The Hierarchy Pattern helps answer:

How does this row relate to another row in the same table?

A self-join lets one table play multiple roles in the same query.

SQL Pattern Series

SQL Pattern Series #1: The Presence Pattern
SQL Pattern Series #2: The Match Pattern
SQL Pattern Series #3: The Missing Data Pattern
SQL Pattern Series #4: The Moving Sum Pattern
SQL Pattern Series #5: The Deduplication Pattern
SQL Pattern Series #6: The Routing Pattern
SQL Pattern Series #7: The Running Total Pattern
SQL Pattern Series #8: The Query Order Pattern
SQL Pattern Series #9: The Period-over-Period Pattern

SQL Bubble Pop

If you are learning SQL or helping others learn SQL, I created SQL Bubble Pop, a mobile game that teaches SQL concepts through quick, interactive challenges and pattern recognition exercises.

The goal is simple:

Learn SQL by recognizing patterns instead of memorizing syntax.