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 implicit default frame or tie-breaking order can silently alter the numbers.

The examples target DuckDB 1.5. Run the setup in a fresh connection; fragments without SELECT illustrate expressions rather than complete queries. Date generation, GROUPS, interval frames and QUALIFY support vary across engines.

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. What Partition, Order, and Frame Control

CREATE TEMP TABLE mission_logs AS
SELECT * FROM (VALUES
 ('M1', DATE '2026-07-01', 1, 10),
 ('M1', DATE '2026-07-01', 2, 20),
 ('M1', DATE '2026-07-03', 3, 5)
) AS t(mission_id, log_date, log_id, resources_used);
CREATE TEMP TABLE dim_calendar AS
SELECT CAST(d AS DATE) AS calendar_date
FROM generate_series(DATE '2026-07-01', DATE '2026-07-31', INTERVAL '1 day') AS t(d);
CREATE TEMP TABLE missions AS
SELECT * FROM (VALUES ('M1','S1',100), ('M2','S1',100), ('M3','S1',90))
AS t(mission_id, system_id, score);
CREATE TEMP TABLE daily_metrics AS
SELECT * FROM (VALUES
 (DATE '2026-07-01',9,10), (DATE '2026-07-02',1,100)
) AS t(calendar_date,successes,attempts);

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. Write the Frame for Running Totals

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.

With ORDER BY log_date alone, the two July 1 rows are peers: DuckDB’s default frame includes both, producing totals 30 and 30. The explicit (log_date, log_id) row order produces 10 and 30, then 35 on July 3. Window ordering does not guarantee result display order; add an outer ORDER BY when that matters. See DuckDB window functions.

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.

After filling the calendar, AVG and SUM still skip NULL values. Seven calendar rows with two unknown days do not establish a complete seven-day estimate. Report coverage, or require seven non-null daily inputs before publishing a full-window metric; the first six rows also have partial history.

4. Build a Calendar Skeleton to Distinguish Missing from Zero

WITH calendar AS (
    -- Use the platform calendar or the corresponding dialect-specific 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 system_id
    ORDER BY score DESC NULLS LAST, 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.

For scores [100,100,90], ranking by score alone gives RANK values [1,1,3] and DENSE_RANK values [1,1,2]. Adding mission_id to the RANK ordering destroys the score ties. Use the unique ID for ROW_NUMBER selection, but retain score-only ordering when ties are the intended result.

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, handle a zero prior value before dividing.

1.0 * (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. For a seven-day overall success rate, both numerator and denominator should be rolled up separately:

SELECT calendar_date,
    1.0 * SUM(successes) OVER seven_rows
    / NULLIF(SUM(attempts) OVER seven_rows, 0) AS rolling_success_rate
FROM daily_metrics
WINDOW seven_rows AS (
    ORDER BY calendar_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
ORDER BY calendar_date;

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.

For the two available days, July 2’s pooled rate is 10/110 ≈ 9.09%, not the average daily rate 45.5%. This is a partial window with only two days. The decimal multiplier avoids integer-division truncation in engines where integer divided by integer remains integer. Add a partition key if several systems share the table, and validate 0 ≤ successes ≤ attempts first.

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
);

In DuckDB 1.5, LAG locates the preceding row in the partition rather than restricting the lookup to the aggregate frame. A shared frame therefore does not make the lag and sum use identical row sets. Check the target engine’s semantics; a CTE can store previous once before an outer query computes the change.

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 system_id
            ORDER BY score DESC NULLS LAST, 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: Tied 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.