SQL LAG() vs Joins & Subquery - with a Simple Real-World Example
When working with time-series or event-based data, one of the most common questions we ask is:
“What changed compared to the previous record?”
Example use cases:
Time between patient readings
Gap between user logins
Delay between transactions
Sensor data intervals
Event latency analysis
Healthcare monitoring intervals
SQL window functions are designed precisely for this kind of problem. They allow you to perform calculations across a set of related rows without collapsing the result set, unlike GROUP BY.
In this blog, we’ll break down SQL window functions using a very simple, practical example.
Assume we have glucose readings from a medical device stored in a table called dexcom.
Table: dexcom
patientid | date_time |
101 | 2024-01-01 08:00:00 |
101 | 2024-01-01 10:00:00 |
101 | 2024-01-01 13:30:00 |
102 | 2024-01-01 09:00:00 |
102 | 2024-01-01 11:00:00 |
Business Question
How much time has passed between consecutive readings for each patient?
The SQL Query using Window function.
select patientid,
date_time,
date_time - lag(date_time)
over (partition by patientid order by date_time) as diff_time
from dexcom;
Let’s break it down step by step.
What Is a Window Function?
A window function performs a calculation across a window of rows related to the current row.
Key characteristics:
Rows are not grouped or reduced
You still see every row
Calculations can reference previous or next rows.
Window functions always use the OVER() clause.
LAG():
The window function we used here is LAG().
The LAG() function allows you to access a value from a previous row.
lag(date_time)
This means:
“Give me the date_time from the previous row”
But previous relative to what?That’s where OVER() comes in.
OVER():
over (partition by patientid order by date_time)
1. PARTITION BY patientid
This tells SQL:
Treat each patient separately
Calculations reset when patientid changes
So patient 101 and patient 102 are handled independently.
2. ORDER BY date_time
This defines the sequence:
Rows are ordered chronologically
“Previous row” now has a clear meaning
Without ORDER BY, LAG() would be meaningless.
Calculating the Time Difference
date_time - lag(date_time) over (...)
Here’s what happens:
SQL fetches the previous date_time
Subtracts it from the current date_time
Produces a time interval (diff_time)
Sample Output
patientid | date_time | diff_time |
101 | 2024-01-01 08:00:00 | NULL |
101 | 2024-01-01 10:00:00 | 02:00:00 |
101 | 2024-01-01 13:30:00 | 03:30:00 |
102 | 2024-01-01 09:00:00 | NULL |
102 | 2024-01-01 11:00:00 | 02:00:00 |
Why is the first diff_time NULL?
Because there is no previous record for the first row of each patient.
How the Database Thinks
At execution time, the engine typically:
Sorts data by patientid, date_time
Processes each partition once
Maintains a small in-memory buffer for previous rows
Computes LAG() in a single pass
Execution Characteristics
Single table scan
No row explosion
Deterministic ordering
Minimal memory overhead
Optimizer-friendly
This is a linear-time operation relative to the dataset size.
Alternative 1: Self-Join (Previous Row Logic)
select d1.patientid,
d1.date_time,
d1.date_time - max(d2.date_time) as diff_time
from dexcom d1
left join dexcom d2
on d1.patientid = d2.patientid
and d2.date_time < d1.date_time
group by d1.patientid, d1.date_time;
How it works
For each row (d1), find earlier timestamps (d2)
Take the maximum earlier timestamp
Subtract it from the current timestamp
Why this is logically correct
It mimics the “previous row” concept
Produces the same result set
Why it is not best
Harder to read and maintain
Requires a GROUP BY
Can be much slower on large datasets
Easy to introduce bugs (missing join conditions)
Alternative 2: Correlated Subquery
select d1.patientid,
d1.date_time,
d1.date_time - (
select max(d2.date_time)
from dexcom d2
where d2.patientid = d1.patientid
and d2.date_time < d1.date_time
) as diff_time
from dexcom d1;
How it works
For each row, find the most recent prior timestamp
Subtract it from the current row
Why this is correct
Accurately models the “previous event” logic
Works even without window function support
Why it is not best
Executes subquery per row (often expensive)
Less expressive than LAG()
Harder for query optimizers to optimize
Alternative 3: Using ROW_NUMBER() + Self-Join
with ordered_data as (
select patientid,
date_time,
row_number() over (partition by patientid order by date_time) as rn
from dexcom
)
select c.patientid,
c.date_time,
c.date_time - p.date_time as diff_time
from ordered_data c
left join ordered_data p
on c.patientid = p.patientid
and c.rn = p.rn + 1;
How it works
Assigns an explicit sequence number per patient
Joins each row to its previous row
Why it is correct
Deterministic ordering
Clear relationship between rows
Why it is not best
More verbose
Requires extra memory
Adds cognitive overhead
Still inferior to LAG() for clarity
Execution Plan Comparison
Window Function Plan
Seq Scan → Sort → Window Aggregate → Output
Self-Join Plan
Seq Scan
↘
Hash/Loop Join → Aggregate → Output
↗
Seq Scan
More operators = more CPU, memory, and risk
Why Optimizers Prefer Window Functions
Modern SQL optimizers (Postgres, Snowflake, BigQuery, SQL Server, Oracle):
Recognize window functions as ordered analytics
Avoid unnecessary joins
Push down predicates
Minimize intermediate data
Joins + aggregates:
Are harder to reorder
Require larger temporary datasets
Increase spill-to-disk risk
Correctness: Semantic vs Accidental
Window Function
lag(date_time) over (partition by patientid order by date_time)
This explicitly means:
“Previous event for this patient in time order.”
Join-Based Logic
max(date_time) where date_time < current
The correctness of the output created by this depends on:
No duplicates
Correct join conditions
Proper grouping
Window functions encode intent, not just logic
Maintainability & Extensibility
Want to add:
Next event? → LEAD()
Two rows back? → LAG(col, 2)
Rolling averages? → AVG() OVER (...)
Gaps larger than X? → simple CASE
With joins, each enhancement:
Adds complexity
Adds new joins or subqueries
Increases bug risk
When Are Joins Acceptable?
Joins are still valid when:
Your SQL engine does not support window functions
You are working with very small datasets
You need to compare different tables
But for row-to-row analytics on the same dataset, joins are a workaround—not a best practice.
Conclusion :
If your problem involves comparing a row with previous or next rows, window functions are the correct abstraction.
They are:
Faster
Safer
More readable
Easier to extend
Optimizer-friendly
