Add window function use cases to the TPC-DS example
- Dominant language
- Python
- Stars
- 2.1k
- Forks
- 267
- Avg merge
- 4d 20h
- Merged PRs (30d)
- 24
Description
## Problem
Window functions are already defined in both the [Expression Language proposal](https://docs.google.com/document/d/1Jn98kHWsnbvo1MycQBAnWirGTwRmw-DWaen7coNCYWA/edit?tab=t.0) and the [core expression language specification](https://github.com/apache/ossie/blob/main/core-spec/expression_language.md#window-functions). The specification covers ranking functions, offset functions, and window aggregations, but the canonical [`examples/tpcds_semantic_model.yaml`](https://github.com/apache/ossie/blob/main/examples/tpcds_semantic_model.yaml) currently demonstrates only regular aggregate metrics.
Without model-level examples, implementers and users do not have a concrete reference for expressing common analytical use cases, including ordering, partitioning, explicit window frames, field qualification across relationships, and the expected query grain.
## Proposed change
Add a small, representative set of validated ANSI SQL window-function use cases to `examples/tpcds_semantic_model.yaml`, using the existing TPC-DS datasets and relationships. Suggested cases:
- Running/cumulative sales through each calendar date (window aggregation)
- Sales rank by brand or store (ranking function)
- Sales change versus the previous period (offset function such as `LAG`)
For example, a cumulative sales metric could be expressed as:
```yaml
- name: cumulative_sales
expression:
dialects:
- dialect: ANSI_SQL
expression: SUM(store_sales.ss_ext_sales_price) OVER (ORDER BY date_dim.d_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
description: Running total of sales revenue through each calendar date
datatype: Decimal
```
Contributor guide
Research direction
Start with examples/tpcds_semantic_model.yaml and compare its existing metrics with the window-functions sections in core-spec/expression_language.md. Use the existing TPC-DS datasets and relationships to add representative cumulative, ranking, and period-change cases covering ordering, partitioning, frames, and query grain. Done means the YAML contains validated ANSI_SQL examples with descriptions and datatypes.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- data
- Issue type
- Feature
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 70/100