DEV Community: Baldwin Apps

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

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

SQL Pattern Series #6: The Routing Pattern

Baldwin Apps — Tue, 16 Jun 2026 14:37:00 +0000

Sometimes SQL decides what happens next

SQL Pattern Series #6 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 SQL can make decisions
When IF and ELSE become useful
Why stored procedures often contain routing logic
How to think about SQL as a decision engine

Many developers first learn SQL as a query language.

SELECT *
FROM Users;

Retrieve data.

Filter data.

Sort data.

Aggregate data.

But eventually you discover something interesting:

SQL can also decide what happens next.

The Problem

Imagine an application sends a request to a stored procedure.

The action could be:

retrieve users
add a user
delete a user

The database must determine which action to perform.

That means the query is no longer just retrieving data.

It is making a decision.

The Routing Pattern

The Routing Pattern uses conditional logic to direct execution down different paths.

For example:

IF @action = 'GET'
    EXEC get_users;

ELSE IF @action = 'ADD'
    EXEC add_user;

ELSE
    EXEC delete_user;

Note: The example shown uses SQL Server (T-SQL) syntax. Conditional and procedural syntax varies between database systems, but the underlying pattern—routing execution based on a condition—exists in most platforms.

The database evaluates a condition.

Based on the result, execution follows a specific route.

Just like a road sign directs traffic.

Thinking About Routing

Many SQL developers think primarily in terms of:

tables
rows
queries

The Routing Pattern introduces another perspective:

decisions
actions
execution paths

The question changes from:

What data should I retrieve?

to:

What should happen next?

Example

Imagine a user management system.

IF @action = 'GET'
    EXEC get_users;

Retrieve existing users.

ELSE IF @action = 'ADD'
    EXEC add_user;

Create a new user.

ELSE
    EXEC delete_user;

Remove a user.

A single procedure can direct execution to multiple outcomes.

Why This Pattern Matters

Routing logic appears frequently in:

stored procedures
ETL processes
administrative scripts
maintenance jobs
workflow automation

The database often becomes responsible for coordinating actions rather than simply returning data.

A Note on Complexity

Routing logic can be extremely useful.

But it can also become difficult to maintain if too many branches are added.

For example:

IF ...
ELSE IF ...
ELSE IF ...
ELSE IF ...
ELSE IF ...

Large decision trees can become hard to follow.

As routing logic grows, consider whether responsibilities should be split into separate procedures or services.

When I Reach for This Pattern

I typically use the Routing Pattern when:

multiple actions share a common entry point
a stored procedure must support different behaviors
execution depends on user input
workflow decisions belong close to the data

Examples include:

user management procedures
import pipelines
administrative tooling
automation workflows

Key Takeaway

SQL is not limited to retrieving data.

Sometimes it acts as a decision engine.

The Routing Pattern helps answer:

Which action should happen next?

rather than:

Which rows should be returned?

That shift opens up a different way of thinking about database programming.

SQL Pattern Series

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 #5: The Deduplication Pattern

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

Keeping the row you want and removing the rest

SQL Pattern Series #5 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 records
Why duplicates are often unavoidable in real systems
How ROW_NUMBER() helps isolate the record you want to keep
A common pattern for deduplicating data

Most developers eventually encounter a table that contains duplicate records.

Sometimes the duplicates are accidental.

Sometimes they are the result of imports, integrations, retries, or application bugs.

The challenge is usually not finding the duplicates.

The challenge is deciding:

Which row should survive?

The Problem

Imagine a Users table:

UserID	Email	CreatedDate
1	alice@example.com	2024-01-01
2	alice@example.com	2024-03-15
3	bob@example.com	2024-02-01

The email address appears more than once.

If only one row should remain, which one do we keep?

The oldest?

The newest?

The one with the highest ID?

The answer depends on the business rule.

Once that rule is defined, the Deduplication Pattern becomes useful.

The Deduplication Pattern

The most common approach uses:

ROW_NUMBER()

combined with:

PARTITION BY

to assign a sequence number to each duplicate group.

For example:

ROW_NUMBER() OVER (
    PARTITION BY Email
    ORDER BY CreatedDate DESC
)

This creates groups based on Email.

Within each group, rows are numbered.

The newest record receives:

rn = 1

The next receives:

rn = 2

and so on.

Example

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (
               PARTITION BY Email
               ORDER BY CreatedDate DESC
           ) AS rn
    FROM Users
) t
WHERE rn = 1;

This returns:

The most recent record for each email address.

All other duplicates are excluded.

Why This Pattern Matters

Deduplication is one of the most common data-cleaning tasks.

You'll encounter it when:

importing data
merging systems
cleaning customer records
preparing analytics datasets
fixing application issues

The pattern allows you to express exactly which record should survive.

That makes the logic clear and repeatable.

Window Functions Make This Easy

Before window functions became widely available, deduplication often required:

self joins
nested subqueries
temporary tables

Window functions simplified the process dramatically.

By combining:

PARTITION BY

with:

ROW_NUMBER()

you can isolate duplicates in a single query.

Choosing the Winning Row

The most important part of the pattern is often:

ORDER BY

For example:

ORDER BY CreatedDate DESC

keeps the newest row.

But you could also choose:

ORDER BY CreatedDate ASC

to keep the oldest.

Or:

ORDER BY UserID DESC

to keep the highest ID.

The ordering defines the business rule.

A Note on Ties

One subtle detail:

ORDER BY CreatedDate DESC

works well when every CreatedDate value is unique.

But what happens if two rows share the same timestamp?

Email                CreatedDate
-------------------  -------------------
alice@example.com    2024-03-15 10:00:00
alice@example.com    2024-03-15 10:00:00

In that case, ROW_NUMBER() will still assign:

rn = 1
rn = 2

but the database may choose either row as the winner.

If the result must be predictable, add a tiebreaker:

ROW_NUMBER() OVER (
    PARTITION BY Email
    ORDER BY CreatedDate DESC,
             UserID DESC
)

Now the ordering is deterministic.

When multiple rows share the same timestamp, the row with the highest UserID wins consistently.

When I Reach for This Pattern

I typically use the Deduplication Pattern when:

duplicate records exist
I need one row per business entity
data quality issues must be cleaned
multiple systems have been merged

Examples include:

duplicate customer records
duplicate email addresses
repeated imports
overlapping transaction data

Key Takeaway

Finding duplicates is only half the problem.

The real question is:

Which row should survive?

The Deduplication Pattern provides a repeatable way to answer that question.

By combining:

ROW_NUMBER()

with:

PARTITION BY

you can identify the winning row and remove the rest.

SQL Pattern Series

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 #4: The Moving Sum Pattern

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

Seeing what is happening over the last N rows

SQL Pattern Series #4 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 moving sum is
How window functions make moving calculations possible
Why moving sums are useful for trend analysis
When to use ROWS BETWEEN

Most reports start with simple totals.

SELECT SUM(SalesAmount)
FROM SalesData;

But eventually a different question appears:

What has happened over the last 30 days?

or:

What is the rolling total over the last 30 transactions?

That is where the Moving Sum Pattern becomes useful.

The Problem

Looking at individual rows often hides the bigger picture.

For example:

Date	Sales
Jan 1	100
Jan 2	120
Jan 3	95
Jan 4	140

Individual values move up and down.

Sometimes what matters is the cumulative behavior over a recent window.

Questions such as:

What were sales during the last 30 days?
How many errors occurred during the last 100 events?
What is the rolling total of customer purchases?

require more context than a single row provides.

The Moving Sum Pattern

A moving sum calculates the total of the current row plus a specified number of previous rows.

For example:

SUM(value) OVER (
    ORDER BY date
    ROWS BETWEEN 29 PRECEDING
         AND CURRENT ROW
)

Conceptually this creates a window.

As each row is processed, the window moves forward.

Rows enter the window.

Older rows leave the window.

The calculation updates automatically.

Example

SELECT
    date,
    value,
    SUM(value) OVER (
        ORDER BY date
        ROWS BETWEEN 29 PRECEDING
             AND CURRENT ROW
    ) AS moving_sum
FROM SalesData;

This produces a rolling 30-row total.

Each result includes:

the current row
the previous 29 rows

The window moves forward one row at a time.

Why This Pattern Matters

Moving sums help reveal trends that individual rows often hide.

Instead of asking:

What happened today?

you begin asking:

What has happened recently?

This makes the pattern useful for:

sales reporting
financial analysis
monitoring systems
operational dashboards
customer activity tracking

The result is often smoother and easier to interpret than raw values alone.

Window Functions Change Everything

Many SQL developers first encounter moving sums when learning window functions.

Without window functions, moving calculations often require:

self joins
correlated subqueries
temporary tables

Window functions provide a cleaner solution.

The Moving Sum Pattern is one of the most common examples.

A Note on Rows vs Dates

One important detail:

ROWS BETWEEN 29 PRECEDING

means:

Previous 29 rows

not:

Previous 29 days

Those are different concepts.

If dates contain gaps, weekends, or missing records, row counts and date ranges may not align.

Always verify that the window matches the business requirement.

When I Reach for This Pattern

I typically use the Moving Sum Pattern when:

I need rolling totals
I want to smooth noisy data
I need trend visibility
The analysis depends on recent activity

Examples include:

rolling sales totals
rolling transaction counts
rolling inventory movement
rolling customer activity

Key Takeaway

Individual rows show events.

Moving sums show context.

The Moving Sum Pattern helps answer:

What has happened recently?

instead of:

What happened on this row?

That shift often makes trends easier to see and decisions easier to make.

SQL Pattern Series

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 #3: The Missing Data Pattern

Baldwin Apps — Sat, 06 Jun 2026 14:39:00 +0000

Finding rows that disappear because of the wrong JOIN

SQL Pattern Series #3 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 rows sometimes disappear unexpectedly
The difference between INNER JOIN and LEFT JOIN
How to identify missing related data
When to use the Missing Data Pattern

Most SQL developers eventually encounter a report that looks wrong.

The numbers seem too low.

Rows appear to be missing.

And yet the query runs successfully.

Often the issue is not the data.

It's the JOIN.

The Question Behind the Query

Many SQL queries involve combining information from multiple tables.

The key question becomes:

Do I only want matching rows?

or:

Do I want all rows from one table, even when no match exists?

Those questions lead to very different results.

Matching Rows Only

INNER JOIN

Returns only rows that exist in both tables.

Keep All Rows

LEFT JOIN

Returns all rows from the left table, even when no matching row exists in the right table.

Rows without a match return NULL values for the right-side columns.

The Missing Data Pattern

The Missing Data Pattern appears when data seems to have vanished from a result set.

In many cases, nothing is actually missing.

The query is simply filtering rows through an INNER JOIN.

Imagine a list of orders.

Some orders have matching customers.

Some do not.

An INNER JOIN only returns the orders with matching customer records.

The remaining rows disappear from the result.

A LEFT JOIN reveals those unmatched rows.

The image above summarizes the core idea:

INNER JOIN returns only matching rows
LEFT JOIN keeps all rows from the left table
Missing matches appear as NULL
Sometimes the missing rows are the most important rows

Example Using INNER JOIN

SELECT o.OrderID,
       c.CustomerName
FROM Orders o
INNER JOIN Customers c
    ON o.CustomerID = c.ID;

This query returns only orders that have a matching customer record.

Any order without a match is excluded.

Conceptually, SQL asks:

Show me only the rows that exist in both tables.

Example Using LEFT JOIN

SELECT o.OrderID,
       c.CustomerName
FROM Orders o
LEFT JOIN Customers c
    ON o.CustomerID = c.ID;

This query keeps every order.

If a matching customer does not exist, the customer columns return NULL.

Conceptually, SQL asks:

Show me every order, whether a matching customer exists or not.

Why This Pattern Matters

Many reporting problems are not caused by bad data.

They are caused by accidental filtering.

Developers often write an INNER JOIN without realizing that unmatched rows are being removed.

The Missing Data Pattern encourages you to ask:

What rows am I losing?
Should unmatched rows be visible?
Is the absence of a match important information?

Sometimes the missing rows are exactly what you're trying to find.

A Note on Investigation

When a report seems incomplete, one of the first things I check is the JOIN type.

A quick comparison between:

INNER JOIN

and

LEFT JOIN

can often reveal whether rows are being filtered unintentionally.

This is especially useful when troubleshooting:

incomplete reports
missing customers
orphaned records
failed imports
data quality issues

When I Reach for This Pattern

I often think about the Missing Data Pattern when I need to find:

customers without orders
orders without customers
employees without managers
products without sales
records missing related data

In these situations, the absence of a relationship is often the most valuable information.

Key Takeaway

When data seems to disappear, ask:

Is the data actually missing?

or:

Is my JOIN hiding it?

Sometimes changing:

INNER JOIN

to:

LEFT JOIN

completely changes what you discover.

And sometimes the missing rows are the story.

SQL Pattern Series

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 #2: The Match Pattern

Baldwin Apps — Tue, 02 Jun 2026 16:31:00 +0000

Choosing between exact matches and pattern matches

SQL Pattern Series #2 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 exact matching and pattern matching
When to use =
When to use LIKE
Why a small operator change can change the meaning of a query

Most SQL developers run into this distinction eventually.

= and LIKE solve different problems.

= checks for an exact value.

LIKE checks whether a value fits a pattern.

That difference matters when the question changes from:

Is this exactly Admin?

to:

Does this start with Admin?

The image above summarizes the core idea:

= asks whether a value is exactly equal.
LIKE asks whether a value matches a pattern.
Both are useful, but they answer different questions.

Exact Match Example

Use = when the value must match exactly.

SELECT *
FROM Users
WHERE Role = 'Admin';

This query asks:

Is the role exactly Admin?

It will match:

Admin

But it will not match:

AdminAssistant
Administrator
SuperAdmin

That is the point.

The = operator is precise.

Pattern Match

Use LIKE when the value may vary but still follows a recognizable pattern.

SELECT *
FROM Users
WHERE Role LIKE 'Admin%';

This query asks:

Does the role start with Admin?

It may match values like:

Admin
AdminAssistant
Administrator

The % wildcard means:

Any number of characters may appear here.

So Admin% means:

Starts with Admin.

The Match Pattern

The Match Pattern is about choosing the right kind of comparison for the question being asked.

Sometimes you need precision.

Sometimes you need flexibility.

The pattern is simple:

Use = for exact matches
Use LIKE for pattern-based matches

But the effect can be significant.

A query that is too strict may miss valid rows.

A query that is too broad may return rows you did not intend to include.

Why This Pattern Matters

This distinction shows up in many real-world queries.

For example:

Find one exact status
Find names that start with a prefix
Find emails from a specific domain
Find product codes with a shared pattern
Find log messages containing a phrase

Each of these questions requires a different kind of match.

Common Examples

Exact status match

SELECT *
FROM Orders
WHERE Status = 'Pending';

This asks for orders where the status is exactly Pending.

Prefix match

SELECT *
FROM Users
WHERE Username LIKE 'test%';

This asks for usernames that start with test.

Contains match

SELECT *
FROM Products
WHERE ProductName LIKE '%keyboard%';

This asks for product names that contain keyboard anywhere in the value.

Suffix match

SELECT *
FROM Customers
WHERE Email LIKE '%@example.com';

This asks for email addresses that end with @example.com.

A Note on Performance

Pattern matching can be powerful, but it can also affect performance.

For example:

WHERE Username LIKE 'Admin%'

may be easier for a database to optimize because the pattern has a fixed beginning.

But this:

WHERE Username LIKE '%Admin%'

can be more expensive because the database may need to search inside the value rather than starting from the beginning.

The exact behavior depends on:

the database system
indexes
collation settings
data size
query plan

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

When I Reach for This Pattern

I typically use = when:

I need one exact value
The column contains controlled values
The comparison should be strict
The query should not include partial matches

I typically use LIKE when:

I need prefix matching
I need suffix matching
I need contains matching
The data is text-based and variable
I am intentionally searching for a pattern

Key Takeaway

Small operator difference.

Very different behavior.

When writing SQL, ask:

Am I looking for an exact value?

or:

Am I looking for a pattern?

That question determines whether the query should use = or LIKE.

The better you understand the match you need, the easier it becomes to write queries that return the right rows.

SQL Pattern Series

This article is part of the SQL Patterns 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 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 #1: The Presence Pattern

Baldwin Apps — Sat, 30 May 2026 03:26:55 +0000

Thinking in terms of existence instead of lists

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

When EXISTS and IN solve the same problem
The difference between set membership and existence
Why the underlying mental model matters
When I typically reach for EXISTS

Most SQL developers write a query like this at some point:

SELECT c.CustomerID,
       c.CustomerName
FROM Customers c
WHERE c.CustomerID IN (
    SELECT o.CustomerID
    FROM Orders o
);

And it works.

But sometimes it isn't the best way to think about the problem.

The Question Behind the Query

Many SQL problems can be framed in two different ways.

Set Membership

Is this value in a set?

WHERE CustomerID IN (...)

Existence

Does at least one matching row exist?

WHERE EXISTS (...)

Both approaches often return the same result.

But they represent different mental models.

The Presence Pattern

The Presence Pattern is useful when you do not actually care about the values being returned from a related table.

You only care whether a matching row exists.

For example:

Customers who have placed an order
Users who have logged in
Employees assigned to a project
Products that have sales

In these cases, the question is often:

Does a related row exist?

rather than:

What values are contained in this list?

The image above summarizes the core idea:

EXISTS asks whether at least one matching row exists
IN checks whether a value belongs to a set
Both often return the same result
The difference is usually the mental model

Example Using EXISTS

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

The subquery is correlated to the outer query.

Conceptually, SQL asks:

For this customer, does at least one matching order exist?

As soon as the answer becomes true, the condition is satisfied.

Why This Pattern Matters

Many SQL developers initially learn syntax.

Over time, they discover that query writing is really about choosing the right mental model.

The Presence Pattern encourages you to think in terms of:

existence
relationships
matching rows

instead of building lists unnecessarily.

That shift often makes queries easier to reason about.

A Note on Performance

Modern database optimizers are extremely sophisticated.

In many systems, IN and EXISTS may be rewritten into similar execution plans.

As a result:

The same result does not necessarily mean the same execution strategy.

And the same syntax does not necessarily mean different performance.

Always validate assumptions with execution plans and real-world testing.

The value of this pattern is primarily conceptual:

existence vs. membership
relationship vs. list
presence vs. values

When I Reach for This Pattern

I typically consider EXISTS when:

I only need to know whether related data exists
The subquery may return many rows
The relationship itself is the focus of the query
I want the query to communicate intent clearly

Examples include:

customers with orders
users with activity
products with transactions
accounts with associated records

Key Takeaway

Many SQL problems become easier when you ask:

Do I need the values?

or:

Do I simply need to know whether they exist?

That small distinction changes how you think about the query.

And sometimes, changing how you think about the problem is more important than changing the syntax.

SQL Pattern Series

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.