Which truck has the most consistent daily sales?

When would you use this?

Operations asks “Which trucks have the highest average daily sales?” - not the total, but the daily average, so high-volume trucks aren’t just rewarded for operating more days.
You need a metric that says average daily revenue per country when sliced by country, but average daily revenue per truck when sliced by truck - same metric, adaptive grouping.
Finance wants to compare daily performance across regions that operate different numbers of days.

What this recipe builds

Two metrics demonstrating the dynamic grouping pattern. The inner metric uses GROUP BY (*, order_header.order_date) - the * means whatever the user is grouping by, plus date - so the inner SUM always computes daily totals regardless of the outer dimension. The outer AVG then averages those daily values.

This recipe uses the nested aggregates concept. See the documentation for the full technical reference.

Steps

Daily revenue (inner)

Revenue grouped by user context plus date. The * inherits whatever dimension the user is querying. This is the building block for the average daily revenue metric.

SQL
YAML

SUM(order_detail.price) GROUP BY (*, order_header.order_date)

type: metric
entity: order_detail
name: daily_revenue
display_name: Daily Revenue (inner)
description: |-
  Revenue grouped by the user's context PLUS date - the inner
  building block for avg_daily_revenue.
datatype: float
sql: |-
  SUM(order_detail.price) GROUP BY (*, order_header.order_date)

Average daily revenue

Average revenue per day. Grouped by truck = average daily per truck. Grouped by country = average daily per country. One metric, any slicing.

SQL
YAML

AVG(order_detail.daily_revenue)

type: metric
entity: order_detail
name: avg_daily_revenue
display_name: Average Daily Revenue
description: |-
  Average revenue per day, dynamic to any user grouping.
  Inner SUM fixes daily grain (user context + date), outer AVG
  collapses over days back to just user context.
datatype: float
sql: |-
  AVG(order_detail.daily_revenue)

Example - sliced by country

Country	Avg daily revenue
Australia	$917,928
Germany	$913,841
Brazil	$812,501
India	$780,411
United States	$676,420

Each row shows the average revenue per day for that country. Australia and Germany lead with similar daily averages despite potentially different total revenues - the daily average normalizes for operating days.

Key design notes

Concern	Guidance
Dynamic vs fixed `GROUP BY`	`GROUP BY (*, date)` is dynamic - the inner grain follows user context plus date. This differs from fixed `GROUP BY (order_id)` used in the AOV recipe, which never changes grain.

Dynamic grouping - reference on GROUP BY (*, entity.field)

Quick Start

Semantic Ecosystem

Modeling Reference

Context Layer

Management

Integrations

Security

Release Notes

Which truck has the most consistent daily sales?

When would you use this?

What this recipe builds

Steps

Example - sliced by country

Key design notes

​When would you use this?

​What this recipe builds

​Steps

​Example - sliced by country

​Key design notes

​Related reading

When would you use this?

What this recipe builds

Steps

Example - sliced by country

Key design notes

Related reading