DEV Community: Baldwin Apps

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

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 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.

SQL Pattern Series #9: The Period-over-Period Pattern

Baldwin Apps — Sat, 27 Jun 2026 14:31:00 +0000

Comparing each row to the one that came before it

SQL Pattern Series #9 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 compare a row to the previous row
When to use LAG()
How period-over-period analysis works
Why this pattern is useful for trends and reporting

Many reports answer a simple question:

What happened this month?

But decision-makers often want a different question answered:

How did this month compare to last month?

That is where the Period-over-Period Pattern comes in.

The Problem

Suppose you have monthly revenue data:

Month      Revenue
---------- -------
Jan-2025    10000
Feb-2025    12000
Mar-2025    11000
Apr-2025    15000

Looking at the values individually is useful.

But it doesn't immediately tell you:

Which months increased?
Which months decreased?
By how much?
What trends are emerging?

To answer those questions, each row needs access to the previous row.

The Period-over-Period Pattern

The Period-over-Period Pattern uses window functions to compare each row against a prior period.

Most commonly:

LAG(column) OVER (
    ORDER BY column
)

LAG() retrieves a value from a previous row without requiring a self-join.

Conceptually, SQL is asking:

What was the value in the previous period?

Example Using LAG

SELECT
    Month,
    Revenue,
    LAG(Revenue) OVER (
        ORDER BY Month
    ) AS PreviousRevenue
FROM MonthlyRevenue;

Result:

Month      Revenue   PreviousRevenue
---------- --------- ----------------
Jan-2025    10000    NULL
Feb-2025    12000    10000
Mar-2025    11000    12000
Apr-2025    15000    11000

Each row now has access to the previous period's value.

Calculating the Difference

Once the previous value is available, calculating the change becomes simple.

SELECT
    Month,
    Revenue,
    LAG(Revenue) OVER (
        ORDER BY Month
    ) AS PreviousRevenue,
    Revenue -
    LAG(Revenue) OVER (
        ORDER BY Month
    ) AS RevenueChange
FROM MonthlyRevenue;

Result:

Month      Revenue   PreviousRevenue   RevenueChange
---------- --------- ---------------- -------------
Jan-2025    10000    NULL              NULL
Feb-2025    12000    10000             2000
Mar-2025    11000    12000            -1000
Apr-2025    15000    11000             4000

Now increases and decreases become obvious.

Why This Pattern Matters

Many business questions are really comparison questions:

Revenue growth
Subscriber growth
Daily active users
Download counts
Inventory changes
Website traffic

Looking at raw numbers alone often hides the trend.

Comparing each period to the previous one reveals movement.

A Note on Ordering

The ordering column matters.

For example:

LAG(Revenue) OVER (
    ORDER BY Month
)

The previous value depends entirely on the specified order.

If the ordering is incorrect, the comparison will also be incorrect.

Always verify that the ordering column reflects the actual sequence you intend to analyze.

Beyond One Period

LAG() can also look farther back.

For example:

LAG(Revenue, 3) OVER (
    ORDER BY Month
)

This retrieves the value from three rows earlier.

Useful for:

Quarter-over-quarter comparisons
Seasonal analysis
Historical benchmarking

When I Reach for This Pattern

I typically use the Period-over-Period Pattern when:

comparing revenue trends
analyzing subscriber growth
tracking downloads
measuring engagement changes
identifying sudden increases or decreases

Examples include:

month-over-month revenue
week-over-week traffic
day-over-day activity
year-over-year comparisons

Key Takeaway

Many reports become more useful when you stop asking:

What happened?

and start asking:

What changed?

The Period-over-Period Pattern helps answer that question by giving each row access to the one that came before it.

Sometimes the trend is more important than the value 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 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 #8: The Query Order Pattern

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

Understanding the order SQL actually processes a query

SQL Pattern Series #8 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:

Why SQL does not run strictly from top to bottom
The difference between written order and logical processing order
Why WHERE filters rows before grouping
Why HAVING filters groups after aggregation

SQL queries are usually written like this:

SELECT
    CustomerID,
    COUNT(*) AS OrderCount
FROM Orders
WHERE OrderDate >= '2024-01-01'
GROUP BY CustomerID
HAVING COUNT(*) > 5
ORDER BY OrderCount DESC;

That looks like the order SQL runs in.

But logically, SQL processes the query in a different sequence.

That difference explains a lot of beginner confusion.

The Problem

Many SQL developers first read queries from top to bottom.

That makes sense visually.

The query starts with:

SELECT

So it feels like SELECT must happen first.

But SQL is declarative.

You describe the result you want.

The database decides how to produce it.

Logical processing order helps you understand why certain clauses behave the way they do.

The Query Order Pattern

The Query Order Pattern is the habit of thinking about SQL in logical stages.

A simplified logical order is:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

This is not necessarily the physical execution plan.

The optimizer may rearrange operations internally.

But this logical order explains how the query is interpreted.

FROM: Choose the Data Source

SQL first determines where the data comes from.

FROM Orders

This establishes the table or joined result set that the rest of the query will operate on.

Before rows can be filtered, grouped, or selected, SQL needs a source.

WHERE: Filter Rows

Next, WHERE filters individual rows.

WHERE OrderDate >= '2024-01-01'

This happens before grouping.

That means WHERE cannot filter aggregate values like:

COUNT(*)

because those counts do not exist yet.

WHERE works row by row.

GROUP BY: Create Groups

After row filtering, SQL groups the remaining rows.

GROUP BY CustomerID

Rows with the same CustomerID are collected into groups.

Once groups exist, aggregate functions such as:

COUNT(*)
SUM(amount)
AVG(score)

can be calculated.

HAVING: Filter Groups

HAVING filters groups after aggregation.

HAVING COUNT(*) > 5

This is why HAVING can use aggregate expressions.

At this point, SQL has already grouped the rows and calculated the count for each group.

A useful shortcut:

WHERE filters rows.

HAVING filters groups.

SELECT: Choose the Output

After filtering and grouping, SQL determines which expressions appear in the result.

SELECT
    CustomerID,
    COUNT(*) AS OrderCount

This is where output columns and aliases are produced.

That is why aliases created in SELECT are not always available in earlier clauses such as WHERE.

The alias may not exist yet in the logical processing order.

ORDER BY: Sort the Result

Finally, ORDER BY sorts the result set.

ORDER BY OrderCount DESC;

Unlike WHERE, many database systems allow ORDER BY to reference aliases created in SELECT.

By this point, the output result has been formed, so the alias is available for sorting.

Why This Pattern Matters

Understanding query order helps explain many common SQL surprises:

Why WHERE COUNT(*) > 5 does not work
Why HAVING COUNT(*) > 5 does work
Why some aliases are not available in WHERE
Why filters before grouping affect the final aggregates
Why SQL feels different from procedural programming

Once this pattern clicks, many SQL errors become easier to diagnose.

Written Order vs Logical Order

SQL is written in one order:

SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY

But it is logically interpreted in another:

FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY

This does not mean the database literally executes every query in that exact sequence.

Modern optimizers are far more sophisticated than that.

But the logical order is still useful because it explains the meaning of the query.

When I Reach for This Pattern

I think about the Query Order Pattern when:

a query fails because an alias is not recognized
WHERE and HAVING become confusing
aggregate filters are involved
reports return unexpected totals
learners try to read SQL from top to bottom

Examples include:

filtering grouped reports
calculating counts by customer
filtering sales totals
debugging aggregate queries
explaining why SQL clauses behave differently

Key Takeaway

SQL is written in one order, but understood in stages.

The most useful shortcut is:

WHERE filters rows.

HAVING filters groups.

Once you understand that sequence, many SQL queries become easier to read, debug, and explain.

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 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 #7: The Running Total Pattern

Baldwin Apps — Sat, 20 Jun 2026 14:35:00 +0000

Seeing how values build over time

SQL Pattern Series #7 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 running total is
How SUM() works as a window function
Why ORDER BY matters inside OVER()
When to use running totals in reports and dashboards

Most reports show totals.

But sometimes a single total is not enough.

You may also need to see how that total builds over time.

For example:

What were cumulative sales by day?

or:

How did the balance grow after each transaction?

That is where the Running Total Pattern becomes useful.

The Problem

Imagine a daily sales table:

Region	OrderDate	DailyTotal
West	2024-01-01	100
West	2024-01-02	150
West	2024-01-03	75
East	2024-01-01	200
East	2024-01-02	50

A regular total can tell you the final amount.

But it does not show the path.

A running total shows how the value accumulates row by row.

The Running Total Pattern

The Running Total Pattern uses a window function to calculate a cumulative total.

The common structure looks like this:

SUM(column) OVER (
    PARTITION BY column
    ORDER BY column
)

The SUM() calculates the total.

The OVER() clause defines the window.

PARTITION BY restarts the calculation for each group.

ORDER BY controls the sequence in which values accumulate.

Example

SELECT
    Region,
    OrderDate,
    DailyTotal,
    SUM(DailyTotal) OVER (
        PARTITION BY Region
        ORDER BY OrderDate
    ) AS RunningTotal
FROM DailySales;

This query calculates a running total for each region.

For the West region, the running total builds across West rows.

For the East region, the running total starts over and builds across East rows.

Example Result

Region	OrderDate	DailyTotal	RunningTotal
West	2024-01-01	100	100
West	2024-01-02	150	250
West	2024-01-03	75	325
East	2024-01-01	200	200
East	2024-01-02	50	250

The running total shows the cumulative value after each row.

Instead of only seeing the final total, you can see how the total grows.

Why This Pattern Matters

Running totals are useful because they show progression.

They help answer questions like:

How much has accumulated so far?
When did the total pass a threshold?
How quickly is value building?
How does one group compare to another over time?

This makes the pattern useful for:

sales reporting
finance reports
inventory tracking
account balances
progress dashboards

PARTITION BY Controls the Reset

One important part of the pattern is:

PARTITION BY Region

This tells SQL:

Calculate a separate running total for each region.

Without PARTITION BY, SQL calculates one running total across the entire result set.

That may be correct in some cases.

But if you need one running total per customer, region, account, product, or category, PARTITION BY defines where the total resets.

ORDER BY Controls the Story

The other important part is:

ORDER BY OrderDate

A running total depends on sequence.

If the rows are ordered differently, the running total changes.

That means the ORDER BY inside the window function is not just formatting.

It defines the logic of the calculation.

A Note on Ties

If two rows have the same OrderDate, the database may not have a predictable order between them.

For example:

ORDER BY OrderDate

may not be enough if multiple rows share the same date.

If the result must be deterministic, add a tiebreaker:

SUM(DailyTotal) OVER (
    PARTITION BY Region
    ORDER BY OrderDate,
             SaleID
) AS RunningTotal

Now SQL has a stable order when two rows have the same date.

Running Total vs Moving Sum

The Running Total Pattern is closely related to the Moving Sum Pattern.

But they answer different questions.

A running total asks:

How much has accumulated so far?

A moving sum asks:

What is the total over the most recent window?

Running totals keep building.

Moving sums slide forward.

Both are useful, but they tell different stories.

When I Reach for This Pattern

I typically use the Running Total Pattern when:

values accumulate over time
I need cumulative totals
each row should show progress so far
totals need to restart by group

Examples include:

cumulative sales by region
running account balances
inventory movement over time
cumulative signups
progress toward a goal

Key Takeaway

Regular totals show the final number.

Running totals show how the number builds.

The Running Total Pattern helps answer:

How much has accumulated so far?

That makes it one of the most useful patterns for reports, dashboards, and trend analysis.

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 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.