feat(multi_stage): support median (PERCENTILE_CONT) and dense_rank as multi_stage measure types
- Dominant language
- Rust
- Stars
- 20.8k
- Forks
- 2.1k
- Avg merge
- 1d 2h
- Merged PRs (30d)
- 181
Description
## Summary
`multi_stage` measures currently support a limited set of aggregation types. Two common analytical patterns — **ordered-set aggregates** (`median` / `PERCENTILE_CONT`) and **dense ranking** (`DENSE_RANK`) — are not supported, forcing workarounds with static SQL subqueries that ignore runtime filters.
## What's missing
### 1. `median` / `PERCENTILE_CONT` ordered-set aggregate
There is no `median` type in `multiStageMeasureType`, and no rendering path for `PERCENTILE_CONT` in the multi_stage aggregation SQL. The natural pattern would be:
```yaml
measures:
- name: _inner_order_count
sql: order_id
type: count_distinct
public: false
- name: median_orders_per_customer
sql: "_inner_order_count"
type: median
multi_stage: true
group_by: []
```
Expected SQL:
```sql
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY COUNT(DISTINCT order_id))
```
Today this fails at schema validation (`median` is not a valid `multiStageMeasureType`) and would fall through to `median()` which is not valid SQL on any warehouse.
### 2. `dense_rank` window function
There is no support for `DENSE_RANK() OVER (PARTITION BY ... ORDER BY ...)` as a `multi_stage` type, analogous to the existing `rank` type. The natural pattern would be:
```yaml
measures:
- name: revenue_rank
sql: "{total_revenue}"
type: dense_rank
multi_stage: true
order_by:
- sql: "{total_revenue}"
dir: desc
group_by:
- category
```
Expected SQL:
```sql
DENSE_RANK() OVER (ORDER BY SUM(revenue) DESC)
```
## Why this matters
Without these types, the only workaround is a static SQL subquery that runs against the full table and **ignores any runtime filters** applied by the caller. This is a correctness problem — dashboards with user-scoped filters silently return global (unfiltered) aggregates.
## Related
- #8486 — multi_stage calculations tracking issue (umbrella)
- #9811 — filter-ignoring for multi_stage (adjacent need)
- #6247, #7914 — BigQuery-specific percentile requests (not applicable to other warehouses)
## Implementation note
We have a working implementation of the `median` case (JS schema-compiler + Tesseract) and are happy to submit a PR if this direction is acceptable.
Contributor guide
Assessment
This issue has not been assessed yet.