Writing SQL to Simulate LAG and LEAD Across Partition Boundaries: Handling Edge Cases Where Window Functions Return NULL

Introduction

Here's a scenario you've almost certainly hit in production: you're building a session analysis pipeline, and you want to know how much time elapsed between a user's last event in one session and their first event in the next. You reach for LAG() or LEAD(), partition by session, and then stare at a column full of NULLs at every partition boundary. The window function did exactly what it was designed to do — it stopped at the edge of each partition — but that's not what your business logic needs.

This is the core tension at the heart of today's lesson. LAG() and LEAD() are tremendously powerful, but they're scoped by design. When you partition by a grouping key, the function has no memory of what existed outside that partition. The first row of partition B genuinely cannot see the last row of partition A, even if those rows are logically adjacent in a sequence you care about. For a lot of use cases, that's fine. For time-series analysis, funnel tracking, cross-session user behavior, and slowly changing dimension comparisons, it's a serious limitation.

By the end of this lesson, you'll have a complete toolkit for working around partition boundary NULLs — not by ignoring them, but by understanding why they occur and deliberately constructing queries that reach across those boundaries when your logic demands it. You'll also understand the performance tradeoffs of each approach so you can make informed decisions in production.

What you'll learn:

• Why LAG() and LEAD() produce NULLs at partition boundaries and when that behavior is correct vs. limiting
• How to use self-joins and CTEs to retrieve values from adjacent partitions
• How to implement a "global sequence" approach that sidesteps partitions entirely
• How to combine FIRST_VALUE() and LAST_VALUE() with a secondary join to simulate cross-partition lag
• How to handle multi-row offsets and gaps in data without silently returning wrong answers
• Performance implications of each strategy and when to choose each one

Prerequisites

You should be comfortable with SQL window functions — specifically LAG(), LEAD(), ROW_NUMBER(), and PARTITION BY. If you need a refresher, check out Window Functions: RANK, ROW_NUMBER, and LAG before continuing. You should also understand Common Table Expressions (CTEs) for Cleaner SQL, as we'll use them heavily to structure these multi-step queries. Familiarity with basic JOIN patterns will be assumed throughout.

Why LAG and LEAD Return NULL at Partition Boundaries

Before we engineer around this behavior, let's be precise about what's happening. When you write:

SELECT
    user_id,
    session_id,
    event_time,
    LAG(event_time) OVER (
        PARTITION BY user_id, session_id
        ORDER BY event_time
    ) AS prev_event_time
FROM user_events;

The database engine creates a separate window for each unique (user_id, session_id) combination. Within each window, rows are ordered by event_time, and LAG() looks back one row. The very first row in each window has no predecessor within that window, so it returns NULL. That's not a bug — that's the contract.

The NULL at the boundary is semantically meaningful: it's saying "there is no previous row in this partition." The problem arises when you want to ask a different question: "what was the previous row across the entire user's timeline, regardless of session?"

Let's set up a realistic dataset we'll use throughout this lesson:

-- Create our working dataset: e-commerce clickstream events
CREATE TABLE user_events (
    event_id      BIGINT PRIMARY KEY,
    user_id       INT NOT NULL,
    session_id    INT NOT NULL,
    page          VARCHAR(100) NOT NULL,
    event_time    TIMESTAMP NOT NULL
);

INSERT INTO user_events VALUES
    (1,  42, 101, 'home',     '2024-01-15 09:00:00'),
    (2,  42, 101, 'search',   '2024-01-15 09:02:30'),
    (3,  42, 101, 'product',  '2024-01-15 09:05:00'),
    (4,  42, 102, 'home',     '2024-01-15 14:00:00'),
    (5,  42, 102, 'cart',     '2024-01-15 14:03:00'),
    (6,  42, 102, 'checkout', '2024-01-15 14:07:00'),
    (7,  42, 103, 'home',     '2024-01-16 10:00:00'),
    (8,  42, 103, 'search',   '2024-01-16 10:01:00'),
    (9,  77, 201, 'home',     '2024-01-15 11:00:00'),
    (10, 77, 201, 'product',  '2024-01-15 11:04:00'),
    (11, 77, 202, 'home',     '2024-01-15 16:00:00'),
    (12, 77, 202, 'checkout', '2024-01-15 16:08:00');

Now, suppose we want to find the inter-session gap for each user: how long was the user inactive between their last event in session N and their first event in session N+1?

Running a naive LAG() partitioned by session fails immediately at the first-row-of-each-session boundary. Let's diagnose that first, then fix it.

Approach 1: Extract Boundary Values with Aggregation, Then Join

The most readable and often most performant approach is to stop trying to make LAG() do something it wasn't designed to do, and instead explicitly extract the boundary values you need.

The pattern has three steps:

1. Find the last event of each partition (session)
2. Find the first event of the next partition
3. Join them together

WITH session_boundaries AS (
    SELECT
        user_id,
        session_id,
        MIN(event_time) AS session_start,
        MAX(event_time) AS session_end
    FROM user_events
    GROUP BY user_id, session_id
),
sessions_with_lag AS (
    SELECT
        user_id,
        session_id,
        session_start,
        session_end,
        LAG(session_end) OVER (
            PARTITION BY user_id
            ORDER BY session_start
        ) AS prev_session_end
    FROM session_boundaries
)
SELECT
    user_id,
    session_id,
    session_start,
    prev_session_end,
    EXTRACT(EPOCH FROM (session_start - prev_session_end)) / 60.0 AS gap_minutes
FROM sessions_with_lag
ORDER BY user_id, session_start;

What's happening here: We first collapse each session into a single boundary row using MIN() and MAX(). Then we apply LAG() at the session level, partitioned only by user_id. Now each session is a single row, and LAG(session_end) reaches across sessions cleanly. The NULL you'll see for the first session of each user is now genuinely correct — there is no prior session.

This query returns:

The NULLs on the first session per user are now semantically clean. You can use Understanding SQL NULL Handling: COALESCE, NULLIF, and IS NULL to substitute a default value if your downstream logic requires one.

Approach 2: Global Row Numbering — Removing Partition Boundaries Entirely

Sometimes you don't want to aggregate. You want the actual previous row's value, crossing whatever partition boundary exists. The technique here is to assign a global sequence number across all rows, then use a self-join or a second LAG() on that global number.

WITH globally_sequenced AS (
    SELECT
        event_id,
        user_id,
        session_id,
        page,
        event_time,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY event_time
        ) AS user_seq,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, session_id
            ORDER BY event_time
        ) AS session_seq
    FROM user_events
),
with_cross_boundary_lag AS (
    SELECT
        curr.event_id,
        curr.user_id,
        curr.session_id,
        curr.page,
        curr.event_time,
        curr.session_seq,
        curr.user_seq,
        -- Cross-partition LAG: pull previous event regardless of session
        LAG(curr.event_time) OVER (
            PARTITION BY curr.user_id
            ORDER BY curr.event_time
        ) AS prev_event_time_cross_partition,
        -- Within-partition LAG: only within same session
        LAG(curr.event_time) OVER (
            PARTITION BY curr.user_id, curr.session_id
            ORDER BY curr.event_time
        ) AS prev_event_time_within_session,
        -- Flag: is this the first event in a session?
        CASE WHEN curr.session_seq = 1 THEN TRUE ELSE FALSE END AS is_session_start
    FROM globally_sequenced curr
)
SELECT
    event_id,
    user_id,
    session_id,
    page,
    event_time,
    is_session_start,
    prev_event_time_within_session,
    prev_event_time_cross_partition,
    -- This is NULL within-session but populated cross-partition for session starts
    CASE
        WHEN is_session_start AND prev_event_time_cross_partition IS NOT NULL
        THEN EXTRACT(EPOCH FROM (event_time - prev_event_time_cross_partition)) / 60.0
        ELSE NULL
    END AS time_since_last_session_ended_minutes
FROM with_cross_boundary_lag
ORDER BY user_id, event_time;

This is the core insight: two window functions, two different partition specs, on the same rows. One gives you the within-session view; one gives you the cross-user view. You then use a CASE expression to route each row to the appropriate answer based on whether it's a boundary row.

Here's the same query using a named WINDOW clause in PostgreSQL:

WITH globally_sequenced AS (
    SELECT
        event_id,
        user_id,
        session_id,
        page,
        event_time,
        ROW_NUMBER() OVER user_session_window     AS session_seq,
        ROW_NUMBER() OVER user_global_window       AS user_seq
    FROM user_events
    WINDOW
        user_session_window AS (
            PARTITION BY user_id, session_id ORDER BY event_time
        ),
        user_global_window AS (
            PARTITION BY user_id ORDER BY event_time
        )
)
SELECT
    event_id,
    user_id,
    session_id,
    event_time,
    session_seq,
    LAG(event_time) OVER user_session_window   AS prev_within_session,
    LAG(event_time) OVER user_global_window     AS prev_cross_session
FROM globally_sequenced
WINDOW
    user_session_window AS (
        PARTITION BY user_id, session_id ORDER BY event_time
    ),
    user_global_window AS (
        PARTITION BY user_id ORDER BY event_time
    )
ORDER BY user_id, event_time;

Approach 3: Self-Join on Sequence Numbers for Multi-Row Offsets

Sometimes you need to look back more than one row, or you need the actual Nth event from the previous partition (not just the last one). For these cases, a self-join on sequence numbers gives you precise control.

Suppose your business requirement is: "For each user's checkout event, find the page they viewed exactly 3 events prior — across session boundaries."

WITH sequenced AS (
    SELECT
        event_id,
        user_id,
        session_id,
        page,
        event_time,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY event_time
        ) AS global_seq
    FROM user_events
)
SELECT
    curr.event_id,
    curr.user_id,
    curr.session_id        AS current_session,
    curr.page              AS current_page,
    curr.event_time,
    curr.global_seq,
    prev.page              AS page_3_events_prior,
    prev.session_id        AS prior_session,
    prev.event_time        AS prior_event_time
FROM sequenced curr
LEFT JOIN sequenced prev
    ON  curr.user_id   = prev.user_id
    AND curr.global_seq = prev.global_seq + 3
WHERE curr.page = 'checkout'
ORDER BY curr.user_id, curr.event_time;

The LEFT JOIN here is deliberate. If there are fewer than 3 prior events for a user, you still want the checkout row — you just won't have the lookback value. An INNER JOIN would silently drop those checkouts, which would almost certainly produce wrong aggregate results downstream.

Approach 4: Using FIRST_VALUE and LAST_VALUE to Anchor Boundary Rows

For some problems, you don't need the Nth prior row — you need the absolute last (or first) value from an adjacent partition. The FIRST_VALUE() and LAST_VALUE() functions combined with the right frame specification give you this without a self-join.

This approach works best when you want to stamp every row in a partition with the boundary value from that partition's edges, then use a subsequent join.

WITH session_anchors AS (
    SELECT
        user_id,
        session_id,
        -- Last event time of each session, broadcast to every row in the session
        LAST_VALUE(event_time) OVER (
            PARTITION BY user_id, session_id
            ORDER BY event_time
            ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
        ) AS session_last_event_time,
        -- First event time of each session, broadcast to every row
        FIRST_VALUE(event_time) OVER (
            PARTITION BY user_id, session_id
            ORDER BY event_time
        ) AS session_first_event_time,
        ROW_NUMBER() OVER (
            PARTITION BY user_id, session_id
            ORDER BY event_time
        ) AS session_seq,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY session_id
        ) AS user_session_rank  -- session ordering per user
    FROM user_events
),
boundary_rows AS (
    -- Extract one row per session representing its boundaries
    SELECT DISTINCT
        user_id,
        session_id,
        session_first_event_time,
        session_last_event_time,
        -- Need session ordering by first event time, not session_id
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY session_first_event_time
        ) AS session_order
    FROM session_anchors
),
with_prev_boundary AS (
    SELECT
        b.user_id,
        b.session_id,
        b.session_first_event_time,
        b.session_last_event_time,
        b.session_order,
        LAG(b.session_last_event_time) OVER (
            PARTITION BY b.user_id
            ORDER BY b.session_order
        ) AS prev_session_last_event_time
    FROM boundary_rows b
)
SELECT
    user_id,
    session_id,
    session_order,
    session_first_event_time,
    prev_session_last_event_time,
    CASE
        WHEN prev_session_last_event_time IS NULL THEN 'First session for user'
        ELSE CONCAT(
            ROUND(
                EXTRACT(EPOCH FROM (session_first_event_time - prev_session_last_event_time)) / 3600.0,
                2
            )::TEXT,
            ' hours between sessions'
        )
    END AS inter_session_gap
FROM with_prev_boundary
ORDER BY user_id, session_order;

Approach 5: The Default Value Parameter — When NULL IS the Right Answer

Before we go further with engineering workarounds, let's pause and discuss a case where the right move is actually to use LAG()'s built-in default parameter properly.

LAG() and LEAD() accept a third argument: a default value returned when the offset falls outside the partition. Most developers know this but forget it applies to the partition boundary, not the whole dataset boundary:

SELECT
    user_id,
    session_id,
    event_time,
    -- Default value when LAG returns NULL (first row of partition)
    LAG(event_time, 1, TIMESTAMP '2000-01-01') OVER (
        PARTITION BY user_id, session_id
        ORDER BY event_time
    ) AS prev_event_time_with_default
FROM user_events;

This is useful when you need to guarantee a non-NULL value for downstream calculations and a sentinel value is appropriate. For example, if you're computing "time since last event in session" and want to treat the session start as having a prior event at 00:00:00, this works cleanly.

But use it carefully. A default value can hide partition boundaries from downstream logic that should know about them. If you replace NULLs with a sentinel date, a consumer of that data might not realize the value is synthetic.

Real-World Project: Cross-Session Funnel Analysis

Let's pull all these techniques together into something you'd actually ship. The scenario: you're an analyst at an e-commerce company. Marketing wants to know, for users who eventually reach checkout in session N, what were they doing in session N-1? Specifically: what page did they exit on in the previous session, and how long ago was that?

This is a genuine cross-partition lookup problem. Checkout is in session N; the exit page is in session N-1. You need to bridge the boundary.

-- Step 1: Build full session summaries with session ordering
WITH session_summary AS (
    SELECT
        user_id,
        session_id,
        MIN(event_time)  AS session_start,
        MAX(event_time)  AS session_end,
        COUNT(*)         AS event_count,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY MIN(event_time)
        ) AS session_order
    FROM user_events
    GROUP BY user_id, session_id
),

-- Step 2: Find the exit page (last page viewed) per session
session_exit_pages AS (
    SELECT DISTINCT ON (user_id, session_id)
        user_id,
        session_id,
        page AS exit_page
    FROM user_events
    ORDER BY user_id, session_id, event_time DESC
    -- PostgreSQL syntax; for other DBs use a ROW_NUMBER filter
),

-- Step 3: Identify sessions containing a checkout event
checkout_sessions AS (
    SELECT DISTINCT
        user_id,
        session_id
    FROM user_events
    WHERE page = 'checkout'
),

-- Step 4: Join session summaries with previous session data
sessions_with_prev AS (
    SELECT
        s.user_id,
        s.session_id,
        s.session_start,
        s.session_end,
        s.session_order,
        prev_s.session_id        AS prev_session_id,
        prev_s.session_start     AS prev_session_start,
        prev_s.session_end       AS prev_session_end,
        prev_ep.exit_page        AS prev_session_exit_page,
        prev_s.event_count       AS prev_session_event_count
    FROM session_summary s
    -- Self-join to get previous session for same user
    LEFT JOIN session_summary prev_s
        ON  s.user_id       = prev_s.user_id
        AND s.session_order = prev_s.session_order + 1
    LEFT JOIN session_exit_pages prev_ep
        ON  prev_s.user_id   = prev_ep.user_id
        AND prev_s.session_id = prev_ep.session_id
),

-- Step 5: Filter to checkout sessions and compute metrics
checkout_context AS (
    SELECT
        swp.user_id,
        swp.session_id,
        swp.session_start,
        swp.prev_session_id,
        swp.prev_session_exit_page,
        swp.prev_session_event_count,
        ROUND(
            EXTRACT(EPOCH FROM (swp.session_start - swp.prev_session_end)) / 3600.0,
            2
        ) AS hours_since_last_session,
        CASE
            WHEN swp.prev_session_id IS NULL THEN 'converted_first_session'
            WHEN hours_since_last_session <= 1 THEN 'quick_return'
            WHEN hours_since_last_session <= 24 THEN 'same_day_return'
            ELSE 'multi_day_return'
        END AS return_pattern
    FROM sessions_with_prev swp
    WHERE swp.session_id IN (
        SELECT session_id FROM checkout_sessions
    )
)

SELECT
    user_id,
    session_id,
    session_start,
    prev_session_exit_page,
    prev_session_event_count,
    hours_since_last_session,
    return_pattern
FROM checkout_context
ORDER BY user_id, session_start;

This pipeline is a concrete example of the broader skill you're building: decompose the cross-partition lookup into explicit, joinable pieces. Each CTE has a single responsibility. The Common Table Expressions (CTEs) for Cleaner SQL approach here isn't just stylistic — it makes the data lineage auditable, which matters when a stakeholder questions why a particular row appears in the funnel report.

Performance Implications and When to Use Each Approach

Let's be direct about the costs of each strategy, because "it works" and "it works at scale" are different things.

Aggregation + LAG (Approach 1)

Best for: Any situation where you can reduce to one row per partition before applying the window function. This is almost always the fastest approach because you're running the window function on N partition-level rows instead of M event-level rows.

Avoid when: You need event-level granularity in the final output, not just boundary values.

Global ROW_NUMBER with dual windows (Approach 2)

Best for: When you need event-level output with both within-partition and cross-partition lag values. Two passes of the window function, but both are table scans — typically very efficient.

Avoid when: The partition key you're crossing boundaries on has very high cardinality and no supporting index. The sort step in the window function can be expensive.

Self-join on sequence numbers (Approach 3)

Best for: When you need arbitrary offset lookbacks (N rows back, not just 1). Also useful when the lookback logic is complex and conditional.

Avoid when: Your table is large and you haven't filtered aggressively before the join. A self-join on a 100M row table with poor predicate pushdown will scan 100M × 100M rows conceptually. Filter early, join on indexed columns. Check the Database Performance Tuning: Advanced Indexing Strategies and Query Rewriting for Production Systems article for how to approach index selection for these patterns.

FIRST_VALUE/LAST_VALUE with frame specification (Approach 4)

Best for: When you want to stamp boundary information onto every row within a partition (e.g., "this row belongs to a session that ended at time T"). Avoids a join entirely when you just need the session's own boundary.

Avoid when: You need the adjacent partition's boundary, not the current partition's. That still requires a join or a second window pass.

Hands-On Exercise

Use the user_events table defined at the start of this lesson. Your goal is to answer this question:

"For each user, what is the average time (in minutes) between the last event of one session and the first event of the next session? Only count gaps where the user returned within 24 hours."

Requirements:

1. Write the query using CTEs. Aim for at least 3 CTEs.
2. Use a window function at some stage — don't solve it purely with self-joins.
3. Handle users with only one session gracefully (they should appear in the output with NULL for the average gap, not be excluded).
4. Label rows where the gap exceeded 24 hours as 'long_gap' and exclude them from the average, but still show the user in the result.

Hint: Start with session_boundaries as your first CTE — MIN(event_time) and MAX(event_time) per session. Then use LAG() partitioned by user_id to pull the previous session's end time. Then filter and aggregate.

A complete solution is provided below — but try it yourself first.

WITH session_boundaries AS (
    SELECT
        user_id,
        session_id,
        MIN(event_time) AS session_start,
        MAX(event_time) AS session_end
    FROM user_events
    GROUP BY user_id, session_id
),
sessions_ordered AS (
    SELECT
        user_id,
        session_id,
        session_start,
        session_end,
        LAG(session_end) OVER (
            PARTITION BY user_id
            ORDER BY session_start
        ) AS prev_session_end
    FROM session_boundaries
),
gaps_classified AS (
    SELECT
        user_id,
        session_id,
        session_start,
        prev_session_end,
        EXTRACT(EPOCH FROM (session_start - prev_session_end)) / 60.0 AS gap_minutes,
        CASE
            WHEN prev_session_end IS NULL THEN 'first_session'
            WHEN EXTRACT(EPOCH FROM (session_start - prev_session_end)) / 3600.0 > 24
                THEN 'long_gap'
            ELSE 'short_gap'
        END AS gap_type
    FROM sessions_ordered
)
SELECT
    user_id,
    COUNT(CASE WHEN gap_type = 'short_gap' THEN 1 END)  AS qualifying_gap_count,
    COUNT(CASE WHEN gap_type = 'long_gap'  THEN 1 END)  AS long_gap_count,
    ROUND(
        AVG(CASE WHEN gap_type = 'short_gap' THEN gap_minutes END),
        2
    ) AS avg_short_gap_minutes
FROM gaps_classified
GROUP BY user_id
ORDER BY user_id;

Common Mistakes & Troubleshooting

Mistake 1: Forgetting that LAST_VALUE uses a restricted default frame

This is the number one gotcha in this entire topic area. If you write:

LAST_VALUE(event_time) OVER (PARTITION BY session_id ORDER BY event_time)

You will get the current row's value, not the partition's last value. Every row will show its own event_time as the "last" value. Always add the explicit frame:

LAST_VALUE(event_time) OVER (
    PARTITION BY session_id
    ORDER BY event_time
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

Mistake 2: Self-joining without filtering first

A common performance disaster: writing a self-join on the full event table, then filtering in a WHERE clause. The optimizer may or may not push that filter down before the join. Be explicit:

-- Risky: filter might happen after the join
SELECT ...
FROM user_events curr
JOIN user_events prev ON ...
WHERE curr.user_id = 42;

-- Better: filter in CTEs before joining
WITH target_user AS (
    SELECT * FROM user_events WHERE user_id = 42
)
SELECT ...
FROM target_user curr
JOIN target_user prev ON ...;

Mistake 3: Using session_id ordering instead of time ordering

If session IDs are assigned sequentially, this might work — but it's fragile. Session IDs are often integers assigned by an application server, and two concurrent sessions from the same user can get non-sequential IDs. Always order by a timestamp, not by the session identifier, when building session sequences.

Mistake 4: Treating NULL inter-session gaps as zeros in aggregations

If you use COALESCE(gap_minutes, 0) before averaging, you'll inflate the average for users with only one session or for the first session of every user. NULL in AVG() is correctly ignored; zero is not. Let the NULLs propagate through to aggregation and filter them with CASE WHEN inside the aggregate function if you need conditional averaging.

Mistake 5: Not accounting for ties in the ORDER BY

If two events in a partition have identical timestamps, LAG() behavior is deterministic only if you add a tiebreaker:

LAG(event_time) OVER (
    PARTITION BY user_id, session_id
    ORDER BY event_time, event_id  -- event_id breaks ties
)

Without a tiebreaker, the database is free to order tied rows arbitrarily, and your LAG() values may be non-deterministic across query executions. This is especially relevant if your table collects events from distributed systems where clock skew can produce same-millisecond events.

Summary & Next Steps

You've now got a complete toolkit for handling the most painful edge case in window function work: the partition boundary NULL. Let's summarize what you've built:

• Aggregation + LAG: Reduce to one row per partition, then apply window functions at that level. Cleanest and usually fastest.
• Dual window specs: Apply two LAG() calls with different PARTITION BY clauses to get both within-partition and cross-partition lookbacks simultaneously.
• Self-join on global sequences: Use ROW_NUMBER() over a wide partition to create a global sequence, then join on offsets for arbitrary lookback distances.
• FIRST_VALUE/LAST_VALUE with explicit frames: Stamp boundary values onto every row in a partition for downstream joining, being careful about default frame behavior.
• The default parameter: Use LAG(expr, offset, default) when a sentinel value is appropriate, but document it clearly.

The skill at the center of all of these techniques isn't a specific SQL function — it's the ability to decompose what "previous" or "next" means in your specific business context, and then engineer a query that extracts exactly that meaning without leaning on assumptions baked into a function's default behavior.

Where to go next from here:

• If you're building session analysis at scale, explore SQL for Data Analysis: Cohort Analysis, Funnels, and Retention - Complete Guide to see how these cross-partition lookups compose into full funnel queries
• For time-series work where you're dealing with irregular intervals, date gaps, and slowly changing values, Temporal Data Mastery: Writing Queries for Time-Series, Date Ranges, and Slowly Changing Dimensions extends these patterns to date arithmetic
• If your cross-partition joins are running slow in production, study your execution plans carefully using the guidance in Query Profiling and Statistics in SQL: Using EXPLAIN ANALYZE, Buffer Metrics, and Row Estimates to Diagnose Slow Queries