4.2 Window Functions and Time Series SQL: Sorting, Frames, and Missing Dates

Window functions preserve the original row details while computing rankings, running totals, or lagged values over a related set of rows. But danger lurks here too: the results may appear logically sound on the surface, yet an unexplicitly defined default frame or tie-breaking order can silently alter the numbers.

The following examples use syntax close to standard SQL; date functions and capabilities like QUALIFY must be adjusted according to the specific database platform.

Learning Objectives

• Distinguish between partition, order, and frame;
• Correctly use rank, cumulative, moving window, and LAG;
• Handle tied rankings and missing dates;
• Avoid errors caused by average ratios and duplicate window expressions.

1. Three Dimensions Determine What

SUM(resources_used) OVER (
    PARTITION BY mission_id
    ORDER BY log_date, log_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

• PARTITION BY: Which rows are visible to each other;
• ORDER BY: The order within a partition;
• frame: Which rows from the sorted result are selected for the current row.

Forgetting partitioning can cause aggregation across all tasks. Omitting a unique tie-breaker may result in unpredictable ordering for multiple rows on the same date.

2. Cumulative and Explicit Frame Writing

SELECT
    mission_id,
    log_date,
    log_id,
    resources_used,
    SUM(resources_used) OVER (
        PARTITION BY mission_id
        ORDER BY log_date, log_id
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM mission_logs;

When ORDER BY is present, the default frame depends on the dialect and data type and often involves RANGE and peer rows. For readability and version stability across releases, cumulative and moving window computations should explicitly define ROWS or the required frame.

3. ROWS, RANGE, and GROUPS

• ROWS takes a window based on physical row ordering;
• RANGE processes based on a range of sorted values and peers;
• GROUPS counts peer groups sharing the same sort key.

AVG(daily_total) OVER (
    ORDER BY calendar_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

This shows the current row and the previous six rows, these do not necessarily correspond to seven calendar days. If date data is missing, it simply reflects the seven most recently recorded dates.

A true seven-day window can be achieved by first populating a calendar table or by using database-supported date RANGE syntax. Support for interval frames varies across engines.

4. Build a Calendar Skeleton to Distinguish Missing from Zero

WITH calendar AS (
    -- Use the platform calendar or the corresponding locale-based date generation function
    SELECT calendar_date
    FROM dim_calendar
    WHERE calendar_date BETWEEN DATE '2026-07-01' AND DATE '2026-07-31'
),
daily AS (
    SELECT log_date, SUM(resources_used) AS total_resources
    FROM mission_logs
    GROUP BY log_date
)
SELECT
    c.calendar_date,
    d.total_resources
FROM calendar c
LEFT JOIN daily d ON d.log_date = c.calendar_date
ORDER BY c.calendar_date;

Whether to convert NULL to 0 depends on whether the absence of a row signifies zero value or simply a missing data point in the pipeline. This logic can be linked with a partition integrity table; zeros should only be filled in after confirming that data has fully arrived and is complete.

5. Ranking Functions Handling Ties Differently

ROW_NUMBER() -- Unique serial number per row, enforce distinction even for parallel entries
RANK()       -- Tied same rank, subsequent skip numbers
DENSE_RANK() -- Tie same rank, no skip in subsequent numbering

ROW_NUMBER() OVER (
    PARTITION BY fortress_id
    ORDER BY score DESC, mission_id
)

To ensure a deterministic selection of one row per group, add a stable tie-breaker. If the business logic requires that tied champions be retained, use RANK and preserve all rank=1 values, do not use arbitrary ROW_NUMBER selections that might arbitrarily pick one row over another.

6. LAG Looking at the Previous Row

LAG(resources_used) OVER (
    PARTITION BY mission_id
    ORDER BY log_date
)

The previous sorted record from the prior row does not guarantee it corresponds to the previous day. Gaps in dates, multiple entries on the same day, and late backfills can all alter the interpretation.

First, aggregate to a daily level and fill in the calendar sequence, then LAG, only then can we define a "change from yesterday." Additionally, precautions must be taken to avoid setting the prior value to zero.

(current_value - previous_value) / NULLIF(previous_value, 0)

The first row has no prior value and should remain NULL; assigning zero to it would create a false impression of growth.

7. Don't Average Daily Rates

Common mistake:

AVG(daily_success_rate) OVER (...)

When denominators vary from day to day, simply averaging the daily rates assigns equal weight to each day. This is flawed. For a seven-day overall success rate, both numerator and denominator should be rolled up separately:

SUM(successes) OVER (
    ORDER BY calendar_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
/
NULLIF(
    SUM(attempts) OVER (
        ORDER BY calendar_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ),
    0
)

A macro-average might be the appropriate target, such as when each team contributes equally. The key is to clearly define the weighting, not to default to averaging rates.

8. Reusing Window Definitions

Some dialects support named windows:

SELECT
    resources_used,
    LAG(resources_used) OVER mission_order AS previous,
    SUM(resources_used) OVER mission_order AS running_total
FROM mission_logs
WINDOW mission_order AS (
    PARTITION BY mission_id
    ORDER BY log_date, log_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
);

However, LAG's handling of frame may differ from that of aggregate windows; dialect-specific behavior must be verified. To avoid duplicating expressions, compute previous first in a CTE, then calculate change in the outer query.

9. Window Result Filtering

In the standard logical execution order, window results come after WHERE and are commonly used in subqueries:

WITH ranked AS (
    SELECT
        m.*,
        ROW_NUMBER() OVER (
            PARTITION BY fortress_id
            ORDER BY score DESC, mission_id
        ) AS row_num
    FROM missions m
)
SELECT *
FROM ranked
WHERE row_num = 1;

Some systems support QUALIFY, but do not assume that all dialects provide this feature when migrating queries.

Common Misconceptions

• ORDER BY auto-stabilizes on window boundaries: Parallel keys require a business tie-breaker.
• ROWS 6 PRECEDING means the past seven days: It computes over seven rows.
• LAG must be the previous day's value: It is simply the row above in the sorted order.
• Moving averages can be computed by simply averaging daily rates: Different denominators alter the weighting.

Exercise

1. Construct two records on the same day and compare the presence or absence of a tie-breaker with ROW_NUMBER.
2. Compare a seven-row window with a seven-day window on data missing two date values.
3. Write out the results of RANK and DENSE_RANK applied to [100,100,90].
4. Rewrite the seven-day success rate using rolling numerator and denominator.

Summary

The results of window functions are determined jointly by partitioning, sorting, and frame definitions. Explicit frame specifications, unique sorting keys, and calendar-based scaffolding help eliminate many "results look correct" but actually hidden errors.

The next lesson organizes multi-step queries into testable models and explores grouping sets, versioned dimension tables, execution plans, and query quality gates.