D-WINDOW — Window Metrics Extension Specification¶

Extension datus-ext/1. Executed-verified on DuckDB and the live warehouse corpus (per-dialect status: design/window-metrics-v2.md §1).

Version: 1.3 (datus-ext)

Goals¶

Declarative window metrics: express period-over-period comparison, rolling aggregates, and cumulative (running / period-to-date) values on top of an existing OSI metric definition — without raw window SQL in metric expressions (which the engine rejects, see Boundaries).
100% OSI-valid documents: the extension rides in a Metric's custom_extensions under the DATUS vendor envelope. A standard-OSI consumer sees a plain aggregate metric; dosi --osi-basic ignores the extension with a warning.
Four canonical families, three sugars: every window metric normalizes to an offset (calendar-shifted comparison, backward or forward), a frame (ordered re-aggregation — time- or value-ordered, ROWS or RANGE), a rank (SQL:2003 ranking of the metric value), or a value (SQL:2011 first/last/nth navigation). The pop / rolling / cumulative sugars are pure authoring conveniences that desugar to the same internal form.
Recompute semantics: a window metric is a derived value. When the query's dimensions or time grain change, the engine recomputes the base aggregate at the new grain and re-evaluates the window — it never re-aggregates window outputs.

Table of Contents¶

Carrier and envelope
Enumerations
The window payload
General form — offset
General form — frame
General form — rank
General form — value
Shared modifiers — order / partition
Sugar — pop
Sugar — rolling
Sugar — cumulative
Time-axis resolution
Compilation semantics
Querying window metrics
Validation
Restrictions
Extensibility model and roadmap
Boundaries
Version history

Carrier and envelope¶

The extension is carried on a Metric in custom_extensions, vendor DATUS, as the payload key window. The metric's own expression is the base aggregate that the window derives from — the SQL is written once, and the window layers on it.

- name: revenue_mom_growth
  description: Month-over-month revenue growth rate
  expression:
    dialects:
      - dialect: ANSI_SQL
        expression: SUM(orders.amount)
  custom_extensions:
    - vendor_name: DATUS
      data: '{"v": 1, "window": {"type": "pop", "offset": "1 month"}}'

Default (key absent): the metric is its plain base aggregate — no derivation.

The metric's base expression MUST infer as a single plain aggregate (MetricKind::Aggregate). Ratio and expression metrics cannot carry window in v1 (see Validation).

Enumerations¶

Granularity¶

Time granularities, shared with D-TIME / D-GRAIN.

Value	Notes
`day`
`week`	Week start follows the execution dialect's `DATE_TRUNC('week', …)` convention (ISO Monday on DuckDB).
`month`
`quarter`
`year`

Calculation (offset family)¶

How the current value relates to the offset (reference) value. Let cur = current bucket's base value, prev = the value count × granularity earlier.

Value	Result	NULL behavior
`value`	`prev`	NULL when no bucket exists at the shifted key
`delta`	`cur - prev`	NULL when `prev` is NULL
`percent_change`	`(cur - prev) / prev`, null-safe	NULL when `prev` is NULL or `0`
`ratio`	`cur / prev`, null-safe	NULL when `prev` is NULL or `0`

Division is always non-integer (CAST(… AS DOUBLE)), zero denominators yield NULL (NULLIF(prev, 0)). A NULL window output is semantically load-bearing ("no comparable prior period") — the engine's D-FILL fill_nulls_with is not applied to window outputs.

Function (frame family)¶

Re-aggregation applied over the ordered frame of per-bucket base values. This is registry level W1 — the portable core, not the ceiling; see the function registry for the SQL:2003-aligned levels (statistical aggregates, ranking, value/navigation) and what each unlocks.

Value	Level	Notes
`sum`	W1
`avg`	W1
`min`	W1
`max`	W1
`count`	W1	Counts frame rows (`COUNT(*)` over the frame; under `units: range`, tied rows are peers and share the count). The base value is not an argument.
`stddev_pop` / `stddev_samp`	W2 (1.3)	Explicit `_pop`/`_samp` forms only — bare `STDDEV`/`VARIANCE` mean different things across engines and are rejected.
`var_pop` / `var_samp`	W2 (1.3)
`covar_pop` / `covar_samp` / `corr`	W3 (1.3)	Two-argument: require `second` (general frame form only). Rendered `CORR(second, primary)`. Not available on Redshift as window functions — structured `dialect_unsupported_window_function`.

Metric-series semantics: the frame aggregates the per-bucket metric values, not the underlying source rows. A 3-month rolling sum of a distinct count is the sum of three monthly distinct counts — an entity active in two months counts twice. Frame-level re-deduplication (source_rows evaluation) is out of scope for v1.

The `window` payload¶

window is a JSON object. Its shape is selected by the optional type discriminator:

`type`	Meaning	Normalizes to
(absent)	General form — exactly one of `offset` / `frame`	itself
`pop`	Period-over-period sugar	offset family
`rolling`	Trailing-N-buckets sugar	frame family
`cumulative`	Running / period-to-date sugar	frame family

Top-level schema (all shapes):

Field	Type	Required	Description
`type`	string	No	`pop` \| `rolling` \| `cumulative`; absent = general form
`offset`	object/string	shape-dependent	Offset spec (general form + `pop`)
`calculation`	string	shape-dependent	Calculation (general offset form + `pop`)
`frame`	object	shape-dependent	Frame spec (general form only)
`rank`	object	shape-dependent	Rank spec (general form only, 1.3)
`value`	object	shape-dependent	Value-navigation spec (general form only, 1.3)
`function`	string	shape-dependent	Function (`rolling` / `cumulative`)
`periods`	integer	shape-dependent	Frame width in buckets (`rolling`)
`reset`	string	shape-dependent	Accumulation reset boundary (`cumulative`)

The general form sets exactly one of offset / frame / rank / value.

Forward compatibility is fail-closed by construction:

Unknown keys at the top level of window are ignored — a future family key (say rank) is safe to ignore on a v1 engine, because the document then has neither offset nor frame and fails the exactly-one-family rule with a structured error instead of computing wrong values. An unknown type value is rejected outright.
Unknown keys inside offset / frame are rejected (deny_unknown_fields) — a future modifier key (say partition or order) changes the semantics of an existing family, and silently ignoring it would return wrong numbers. Rejection forces "this engine is too old for this document" to surface as invalid_datus_extension.

Keys that belong to a different shape than the one selected are likewise rejected — e.g. periods with type: pop, or a top-level function in the general form.

General form — `offset`¶

Compare each time bucket with the bucket count × granularity earlier.

{"window": {"offset": {"count": 1, "granularity": "month"},
            "calculation": "percent_change"}}

Schema¶

Field	Type	Required	Description
`offset.count`	integer ≥ 1	Yes	Number of granularity units to shift
`offset.granularity`	string	Yes	Granularity of the shift
`offset.direction`	string	No	`"back"` (default — prior period, LAG) \| `"forward"` (next period, LEAD; 1.3). Object form only: the `"1 month"` string sugar is always back. A forward offset widens the time range's end bound instead of its start, and the output is trimmed back likewise.
`calculation`	string	Yes	Calculation; required in the general form (defaults belong to sugars)

The shift is calendar-correct under gaps: the reference value is the bucket at exactly the shifted calendar key, or NULL if that bucket has no data — never "the previous existing row". granularity may be coarser than the query grain (YoY over monthly buckets: DATE_TRUNC('month', t) - INTERVAL 1 YEAR lands exactly on the prior-year month).

General form — `frame`¶

Re-aggregate the ordered series of per-bucket base values over a trailing frame ending at the current bucket.

{"window": {"frame": {"function": "sum", "preceding": "unbounded", "reset": "year"}}}

Schema¶

Field	Type	Required	Description
`frame.function`	string	Yes	Function
`frame.preceding`	integer ≥ 0 \| `"unbounded"`	Yes	Frame start: `n` = `n PRECEDING`; `"unbounded"` = `UNBOUNDED PRECEDING`. The frame always ends at `CURRENT ROW`.
`frame.reset`	string	No	`week` \| `month` \| `quarter` \| `year` — restart accumulation at each boundary of this granularity. Only meaningful with `"unbounded"`; also accepted with numeric `preceding` (the frame is then clipped at the boundary). Absent = never reset.
`frame.require_full_window`	boolean	No (default `false`)	NULL out buckets whose frame holds fewer than `preceding + 1` rows, instead of aggregating a partial head frame. Finite `preceding` only — with `"unbounded"` and `true` the payload is rejected (an unbounded frame is never partial). Rejected under `units: "range"` (frame-row counting is undefined over tie peers). In-family 1.2 addition: an engine predating it rejects the key (`invalid_datus_extension`), never drops it.
`frame.order`	object	No	Shared modifier (1.3). Default `{"by": "time", "direction": "asc"}` — the v1 behavior. `order.by: "value"` orders by the metric's own per-bucket value and forbids `reset` (a time partition without time order is incoherent).
`frame.partition`	object	No	Shared modifier (1.3). Default mode `query_dimensions` — the v1 behavior.
`frame.units`	string	No	`"rows"` (default) \| `"range"` (1.3). RANGE requires `preceding: "unbounded"` and `order.by: "value"`: a value-ordered cumulative frame where tied values are peers and aggregate together (a time-ordered series has at most one row per bucket, where RANGE equals ROWS).
`frame.second`	string	Iff two-argument	(1.3) The second input series for `covar_pop` / `covar_samp` / `corr`, named as a plain single-aggregate metric of the same model (no window, not ratio/expression). Resolved at compile time; the base stage computes both series. General frame form only.

The frame is row-based over the aggregated series (ROWS BETWEEN … AND CURRENT ROW), where the series has at most one row per partition × time bucket by construction. Missing buckets are not densified in v1: a "3 preceding" frame spans the three previous existing buckets. Offsets do not have this caveat.

reset: day is rejected: day is the finest grain, so a day reset could never be coarser than any query grain.

General form — `rank`¶

The position of each row's metric value within its partition — the SQL:2003 ranking functions, lowered with no frame clause. (1.3.)

{"window": {"rank": {"function": "row_number",
            "partition": {"mode": "query_dimensions_except",
                          "exclude": ["activities.ac_code"]}}}}
{"window": {"rank": {"function": "ntile", "buckets": 4}}}

Schema¶

Field	Type	Required	Description
`rank.function`	string	Yes	`row_number` \| `rank` \| `dense_rank` \| `ntile` \| `percent_rank` \| `cume_dist`
`rank.buckets`	integer ≥ 1	Iff `ntile`	`NTILE` bucket count
`rank.order`	object	No	Shared modifier. Default `{"by": "value", "direction": "desc"}` — the colloquial "top-N".
`rank.partition`	object	No	Shared modifier. Default `none` (one global ranking) — NOT `query_dimensions`: ranking within all non-time dims would rank every row against itself whenever the entity dims all sit in the partition.

Determinism: the positional functions (row_number, ntile) get the engine's dim tie-breakers (the non-partition, non-time dims, ascending — mirroring the benchmark convention ORDER BY value DESC, entity); the peer-aware functions (rank, dense_rank, percent_rank, cume_dist) must not — tie-breaking would dissolve the very peer groups they rank.

With the default value ordering, rank metrics are axis-free: the query needs no time dimension at all. order.by: "time" flips the standard time-axis requirement back on (the bucket-ordinal shape). Rank metrics are partition-global — see Restrictions.

cume_dist is not available on ClickHouse (dialect_unsupported_window_function, structured — never invalid SQL).

General form — `value`¶

Navigate to the first / last / nth metric value of the partition — the SQL:2011 value functions, always lowered with the explicit full frame (ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING): partition-wide navigation, burying the classic trap where LAST_VALUE under the default frame stops at the current row. (1.3.)

{"window": {"value": {"function": "first_value"}}}
{"window": {"value": {"function": "nth_value", "n": 2}}}

Schema¶

Field	Type	Required	Description
`value.function`	string	Yes	`first_value` \| `last_value` \| `nth_value`
`value.n`	integer ≥ 1	Iff `nth_value`	The navigated position
`value.order`	object	No	Shared modifier. Default `{"by": "time", "direction": "asc"}` — navigate the metric series.
`value.partition`	object	No	Shared modifier. Default `query_dimensions`.

NULL handling is always RESPECT NULLS; ignore_nulls stays reserved (SQL:2011 IGNORE NULLS is not portable across the target dialects). Navigation lands on one row, so the dim tie-breakers are always appended. Value metrics are partition-global — see Restrictions.

Dimension navigation ("the earliest activity's code"): give the metric a per-bucket-deterministic base over the dimension (MIN(activities.ac_code) is exact when each bucket holds one activity) and navigate that.

Shared modifiers — `order` / `partition`¶

Two modifier objects open the degrees of freedom v1 froze (Extensibility). Both are in-family 1.3 additions: a 1.2 engine rejects them (deny_unknown_fields), never silently mis-computes.

"order":     {"by": "time" | "value", "direction": "asc" | "desc"}
"partition": {"mode": "query_dimensions" | "query_dimensions_except"
                    | "time_bucket" | "none",
              "exclude": ["dataset.field", ...]}

order.by: the time axis (time) or the metric's own per-bucket value (value). An omitted direction defaults per key: time → asc, value → desc.
partition.mode: which query dims form the PARTITION BY — query_dimensions (all non-time dims), query_dimensions_except (minus exclude), time_bucket (the time bucket itself — each bucket is its own population), or none (one global window). exclude pairs only with query_dimensions_except, must be non-empty, and each entry must be the qualified dataset.field form (validated against the model at compile time; the bare-field spelling stays reserved).
Recompute semantics: an excluded dim absent from a particular query's group_by is a no-op — the window recomputes over whatever is queried.

Sugar — `pop`¶

Period-over-period.

{"window": {"type": "pop", "offset": "1 month"}}
{"window": {"type": "pop", "offset": "1 year", "calculation": "delta"}}
{"window": {"type": "pop", "offset": {"count": 12, "granularity": "month"}, "calculation": "value"}}

Schema¶

Field	Type	Required	Description
`offset`	string \| object	Yes	`"<count> <granularity>"` (e.g. `"1 month"`, `"2 weeks"` — one trailing `s` accepted) or the object form `{count, granularity}`
`calculation`	string	No	Default `percent_change` — the colloquial meaning of "MoM/YoY"

Forbidden keys: frame, function, periods, reset.

Desugaring¶

{type: pop, offset: "1 month"}            ≡ {offset: {count: 1, granularity: month},
                                             calculation: percent_change}
{type: pop, offset: "1 year",
 calculation: delta}                      ≡ {offset: {count: 1, granularity: year},
                                             calculation: delta}

Sugar — `rolling`¶

Trailing window of the last periods buckets (including the current one).

{"window": {"type": "rolling", "function": "avg", "periods": 3}}

Schema¶

Field	Type	Required	Description
`function`	string	Yes	Function
`periods`	integer ≥ 1	Yes	Total buckets in the frame, current included
`require_full_window`	boolean	No (default `false`)	NULL out buckets with fewer than `periods` rows in frame (see the general frame form)

Forbidden keys: offset, calculation, frame, reset.

Desugaring¶

{type: rolling, function: avg, periods: 3} ≡ {frame: {function: avg, preceding: 2}}
                                              -- ROWS BETWEEN 2 PRECEDING AND CURRENT ROW

periods: 1 is the degenerate identity frame (current bucket only).

Sugar — `cumulative`¶

Running accumulation from the series start — or from each reset boundary (period-to-date: YTD / QTD / MTD).

{"window": {"type": "cumulative", "function": "sum"}}
{"window": {"type": "cumulative", "function": "sum", "reset": "year"}}

Schema¶

Field	Type	Required	Description
`function`	string	Yes	Function
`reset`	string	No	`week` \| `month` \| `quarter` \| `year`; absent = never reset (running)

Forbidden keys: offset, calculation, frame, periods.

Desugaring¶

{type: cumulative, function: sum}              ≡ {frame: {function: sum, preceding: "unbounded"}}
{type: cumulative, function: sum, reset: year} ≡ {frame: {function: sum, preceding: "unbounded",
                                                          reset: year}}

Time-axis resolution¶

D-WINDOW declares no time axis of its own — it composes with D-TIME:

The metric's D-TIME time_dimension payload key (same DATUS envelope), if present.
Else the unique primary_time_dimension among the metric's datasets (a dataset's D-TIME declaration, or its single is_time field).
Else the query fails with the structured error no_primary_time_dimension — at query time, not model-compile time, so an axis added later Just Works.

A dataset with several is_time fields therefore needs either a dataset-level D-TIME declaration or a metric-level time_dimension beside window:

custom_extensions:
  - vendor_name: DATUS
    data: '{"v": 1,
            "time_dimension": "activities.start_date",
            "window": {"type": "pop", "offset": "1 month"}}'

Compilation semantics¶

Both families compile to one SQL statement wrapped around the exact grouped aggregate the engine already emits for the base metric (base CTE: requested dimensions + DATE_TRUNC(grain, axis) + base aggregates).

Offset family — shifted-key self-join¶

WITH base AS (SELECT <dims>, DATE_TRUNC('month', t) AS t__month,
                     <base_agg> AS v
              FROM …
              WHERE t >= DATE '<start>' - INTERVAL <count> <granularity>   -- input expansion
                AND t <  DATE '<end>'
              GROUP BY <dims>, DATE_TRUNC('month', t))
SELECT c.<dims>, c.t__month, c.v AS <base_metric>,
       CAST((c.v - p.v) AS DOUBLE) / NULLIF(p.v, 0) AS <window_metric>   -- percent_change
FROM base AS c
LEFT JOIN base AS p
  ON p.<dim> = c.<dim> AND …                                -- partition equality
 AND p.t__month = c.t__month - INTERVAL <count> <granularity>
WHERE c.t__month >= DATE '<start>'                          -- output trim

Input expansion / output trim: the base scan's lower bound is widened by the offset so the first requested bucket has its reference loaded; the final output is trimmed back to the requested range. The upper bound is never widened.
Partition equality uses plain =: a NULL dimension value has no comparable predecessor (intentional).
The self-join (not LAG) is what makes the comparison calendar-correct under gaps.

Frame family — window function¶

WITH base AS (SELECT DATE_TRUNC('month', t) AS t__month, <base_agg> AS v
              FROM … GROUP BY …)
SELECT t__month, v AS <base_metric>,
       SUM(v) OVER (PARTITION BY <dims>                     -- query dims minus the time axis
                    ORDER BY t__month
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS <window_metric>
FROM base

With reset, the partition additionally includes the reset bucket, and the scan / output get the expansion-and-trim treatment so a mid-period query still accumulates from the period start:

WITH base AS (SELECT … WHERE t >= DATE_TRUNC('year', DATE '<start>') AND t < DATE '<end>' …),
     win  AS (SELECT t__month, v,
                     SUM(v) OVER (PARTITION BY <dims>, DATE_TRUNC('year', t__month)
                                  ORDER BY t__month
                                  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd
              FROM base)
SELECT … FROM win WHERE t__month >= DATE '<start>'          -- trim AFTER the window stage

The trim must sit outside the windowed SELECT (SQL evaluates WHERE before window functions; an inner trim would delete the lookback rows the accumulation needs). Reset alignment is computed in SQL (DATE_TRUNC(reset, start_literal)), so the bound and the partition key agree by construction on every dialect — including week, whose start is dialect-defined.

Rollup contract¶

Window metrics recompute. Removing a dimension or changing the grain re-evaluates the base aggregate at the new query grain and re-runs the window over it. Window outputs are never summed/averaged upward, and cannot appear inside other metrics' expressions.

Querying window metrics¶

No new query surface. A window metric is requested by name like any metric; the window's ordering and partition derive from the query:

The time axis MUST appear in group_by with a grain — either the concrete field (activities.start_date:month) or the reserved metric_time:month — else the query fails with unknown_dimension and a retry hint naming the exact group-by item to add.
Every other group-by dimension becomes the window partition (offset join keys / PARTITION BY), so group_by: [region, start_date:month] compares May-vs-April within each region.
time_range bounds the output; the engine widens the input scan as needed (offset lookback, reset alignment) and trims after the window stage.

{"metrics": ["revenue", "revenue_mom_growth"],
 "group_by": ["orders.region", "metric_time:month"],
 "time_range": {"start": "2025-05-01", "end": "2025-11-01"}}

Validation¶

Model-compile time (invalid_datus_extension unless noted):

window must be a JSON object with a valid shape per its type (schemas above); unknown type values list the three sugars. Unknown keys inside the offset / frame objects are rejected (fail-closed forward compatibility, see The window payload).
Exactly one family: general form requires exactly one of offset / frame; sugar shapes forbid the other family's keys (tables above).
offset.count ≥ 1 (both string and object forms); offset string must be "<count> <granularity>".
periods ≥ 1; frame.preceding is a non-negative integer or "unbounded".
reset ∈ {week, month, quarter, year}.
The base expression must infer as a single plain aggregate — Ratio / Expression metrics are rejected.
Raw window SQL in any metric expression remains rejected (window_in_metric) — D-WINDOW is the only window path.

Query time (structured QueryErrors):

Condition	Error
No resolvable time axis	`no_primary_time_dimension`
Axis not in `group_by` with a grain	`unknown_dimension` + retry hint
`reset` finer than the queried time grain	`window_reset_too_fine` (equal grain is allowed — identity)
v1 restriction hit (below)	`not_implemented`

Engine modes: in --osi-basic the whole DATUS envelope is inert — the metric compiles as its plain base aggregate, with one ignored_vendor_extension warning noting that no window derivation applies.

Restrictions¶

All reported as structured not_implemented errors, never silently ignored:

All window metrics in one query that need the time axis share one (offsets always; frames when time-ordered / reset / time_bucket; rank/value when time-ordered). Metrics that order by value are axis-free and may run with no time dimension in the query at all (1.3 family-conditional axis).
Window queries are single-branch: all requested metrics' measures must evaluate from one base dataset (no fan-out merge under a window).
With a time_range.start, a no-reset time-ordered running metric cannot share a query with scan-expanding metrics (offset lookback or reset alignment): the expanded scan would silently change what it accumulates. Query it separately, give it a reset, or drop the start.
A partition-global metric (rank family, value family, any value-ordered frame) cannot share a query with scan-expanding metrics under a time_range — the expanded scan would silently change the population it ranks or aggregates over. Alone with a time_range it is fine: the defined meaning is "within the queried window".
No cross-metric base reference (window derives from the metric's own expression — the W3 second names an input series, not a base), no nested windows, no densification/time-spine.

Relaxed since v1 (mixed families, mixed shifts — one self-join per distinct (count, granularity) — and mixed resets with a start, which align the scan to the coarsest reset; each metric's own reset partition keeps finer resets exact under the over-expanded scan; the combined >= bound chains DATE_TRUNC(coarsest reset, start) with every distinct offset shift, which may over-widen the scan — a cost, never a correctness issue). The remaining relaxation order lives in design/window-metrics-v2.md.

Extensibility model and roadmap¶

Grounding: SQL:2003 (ISO/IEC 9075-2:2003) introduced window functions as two groups — the ranking functions (RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, ROW_NUMBER) and windowed aggregates (every aggregate, including the statistical STDDEV_POP/STDDEV_SAMP/VAR_POP/VAR_SAMP and the two-argument COVAR_*/CORR/REGR_* set); SQL:2008/2011 added the value/navigation functions (FIRST_VALUE/LAST_VALUE/NTH_VALUE, LEAD/LAG) and named windows. v1's surface is deliberately the smallest slice that covers the derived-metric corpus — a floor, not a ceiling. This section is the contract for growing it without breaking documents or semantics.

The extension mechanism¶

A window pattern enters the spec as a named family: a new type value (+ general-form key), a new internal WindowKind variant, and a dedicated lowering with its correctness rules (partitioning, input-range expansion, output trim) built in. The engine seams are additive at every layer: the type discriminator fails closed on old engines (see The window payload), the IR is an open enum, each family is its own plan node, and the SQL layer already composes multi-stage CTEs — the machinery a gaps-and-islands lowering needs exists today.

Named families — rather than a user-authored window-expression DAG — are the deliberate philosophy: streak (below) is not expressible as a single window even in a DAG model, but it is expressible as a family with a canned two-stage lowering. Semantic names keep validation, recompute-on-rollup, and range expansion decidable; raw window specs do not.

Frozen degrees of freedom (and the modifiers that open them)¶

v1 hardcoded three degrees of freedom; 1.3 opened them exactly along the reserved seams — in-family unknown keys were rejected, so the additions are non-breaking and never silently mis-compute on an old engine:

Degree of freedom	v1 default	Opened in 1.3	Still reserved
partition	all non-time group-by dims	`partition: {"mode": "query_dimensions" \\| "query_dimensions_except" (+`exclude`) \\| "time_bucket" \\| "none"}`	—
ordering	time axis, ascending	`order: {"by": "time" \\| "value", "direction": "asc" \\| "desc"}`	NULLS FIRST/LAST placement
frame shape	trailing `ROWS … CURRENT ROW`	`units: "range"` (value-ordered cumulative), the value family's explicit full frame	`following` (centered windows), `GROUPS` units, frame exclusion

Family roadmap¶

Family	Status	Semantics	Lowering shape
`rank`	shipped 1.3	rank / dense_rank / row_number / ntile / percent_rank / cume_dist over the metric value within a partition (the distribution functions ride in the same family)	window function, no frame (per SQL:2003 ranking rules)
`value`	shipped 1.3	`first_value` / `last_value` / `nth_value` navigation	mandatory explicit full frame; RESPECT NULLS (`ignore_nulls` reserved — SQL:2011 `IGNORE NULLS` is not portable)
`share`	future	contribution of each group to a total: `v / SUM(v) OVER (PARTITION BY time_bucket)`	windowed total + safe division (partition modes now exist)
`streak`	future	length of the current run of buckets satisfying a condition (e.g. consecutive growth months)	canned gaps-and-islands: flag → `SUM` reset keys → count within island (two extra CTE stages) — the hardest lowering, not a new mechanism

Cross-metric base references (base: <metric>), densification/time-spine, and require_full_window are orthogonal extensions tracked in design/window-metrics-v2.md.

Function registry¶

Frame-family functions are registered in levels; a level is enabled per engine version only when its lowering is verified (executed oracle on DuckDB, snapshots elsewhere). Using a function above the engine's level is invalid_datus_extension, never a silent fallback.

Level	Functions	Standard	Status
W1 (v1)	`sum`, `avg`, `min`, `max`, `count`	SQL:2003 windowed aggregates (core)	implemented
W2 statistical	`stddev_pop`, `stddev_samp`, `var_pop`, `var_samp`	SQL:2003	implemented 1.3 — per-dialect spellings rendered by semantics (Snowflake `STDDEV`/`VARIANCE_POP`, ClickHouse lowercase aliases)
W3 two-argument	`covar_pop/samp`, `corr` via `frame.second`	SQL:2003	implemented 1.3 — Redshift gated (`Dialect::supports_two_arg_stat_window`); `regr_*`, `percentile_cont/disc`, `median` remain future
Ranking	`rank`, `dense_rank`, `row_number`, `ntile(n)`, `percent_rank`, `cume_dist`	SQL:2003	implemented 1.3 as the `rank` family — ClickHouse `cume_dist` gated
Navigation	`lag`/`lead` (internalized by the offset family's self-join — `direction` picks the side), `first_value`, `last_value`, `nth_value`	SQL:2011	implemented 1.3 (`offset.direction`, the `value` family)

Boundaries¶

Dialect enablement: executed and oracle-verified on DuckDB (reference) and the live warehouse corpus; per-dialect DateSub lowerings and executed status are tracked in design/window-metrics-v2.md §1. reset: week follows each engine's own week start — internally consistent, but engines that do not start weeks on Monday differ from the reference (declared per dialect in the corpus, never silently absorbed).
Out of scope as of 1.3 (roadmap above): the share and streak families, regr_* / percentile / median functions, ignore_nulls, frame exclusion, following bounds and GROUPS frames, source_rows evaluation, fiscal calendars, week_start overrides. (Shipped since v1: ranking, value navigation, W2/W3 statistics, value-ordered RANGE frames, forward offsets, require_full_window.)
Out of scope structurally (not window metrics; model separately if needed): funnel/event-chain shapes, retention/cohort matrices, sessionization, row-pattern recognition (MATCH_RECOGNIZE).
This spec supersedes the ChatGPT draft (window-metircs-spec.md); the rationale for each dropped mechanism is recorded in design/window-metrics-v2.md.

Version history¶

1.0.0.dev0 (2026-08-05): initial draft — offset + frame families, pop/rolling/cumulative sugars, cumulative reset, DuckDB-first.
2026-08-06: all-dialect enablement (per-dialect offset lowerings, live corpus verification — design/window-metrics-v2.md §1).
2026-08-06: mixed families / mixed shifts / mixed resets-with-start relaxed; require_full_window added (in-family 1.2 addition, fail-closed on older engines).
1.3 (2026-08-10): the extensibility roadmap's first wave, closing the datus-benchmark window corpus (Q1–Q11) — the rank family (SQL:2003 ranking, no frame clause), the value family (first/last/nth navigation over the explicit full frame), offset.direction: "forward" (LEAD — end-bound scan expansion + trim), W2 statistical + W3 two-argument frame functions (frame.second), the shared order / partition modifiers, and value-ordered units: "range" frames (ties are peers). Dialect gates: ClickHouse cume_dist, Redshift windowed COVAR/CORR — structured dialect_unsupported_window_function, never invalid SQL. Every addition fails closed on a 1.2 engine by construction.

D-WINDOW — Window Metrics Extension Specification¶

Goals¶

Table of Contents¶

Carrier and envelope¶

Enumerations¶

Granularity¶

Calculation (offset family)¶

Function (frame family)¶

The window payload¶

General form — offset¶

Schema¶

General form — frame¶

Schema¶

General form — rank¶

Schema¶

General form — value¶

Schema¶

Shared modifiers — order / partition¶

Sugar — pop¶

Schema¶

Desugaring¶

Sugar — rolling¶

Schema¶

Desugaring¶

Sugar — cumulative¶

Schema¶

Desugaring¶

Time-axis resolution¶

Compilation semantics¶

Offset family — shifted-key self-join¶

Frame family — window function¶

Rollup contract¶

Querying window metrics¶

Validation¶

Restrictions¶

Extensibility model and roadmap¶

The extension mechanism¶

Frozen degrees of freedom (and the modifiers that open them)¶

Family roadmap¶

Function registry¶

Boundaries¶

Version history¶

The `window` payload¶

General form — `offset`¶

General form — `frame`¶

General form — `rank`¶

General form — `value`¶

Shared modifiers — `order` / `partition`¶

Sugar — `pop`¶

Sugar — `rolling`¶

Sugar — `cumulative`¶