> ## Documentation Index
> Fetch the complete documentation index at: https://honeydew.ai/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Which truck has the most consistent daily sales?

> Revenue per day that adapts to any dimension your users slice by

## 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.

<Tip>
  This recipe uses the [nested aggregates](/docs/calculations/metrics#dynamic-grouping-adding-groups-and-nested-aggregation) concept.
  See the documentation for the full technical reference.
</Tip>

## Steps

<Steps>
  <Step title="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.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        SUM(order_detail.price) GROUP BY (*, order_header.order_date)
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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)
        ```
      </Tab>
    </Tabs>
  </Step>

  <Step title="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.

    <Tabs>
      <Tab title="SQL">
        ```sql theme={null}
        AVG(order_detail.daily_revenue)
        ```
      </Tab>

      <Tab title="YAML">
        ```yaml theme={null}
        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)
        ```
      </Tab>
    </Tabs>
  </Step>
</Steps>

### 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](/docs/recipes/avg-order-value), which never changes grain. |

## Related reading

* [Dynamic grouping](/docs/calculations/metrics#dynamic-grouping-removing-groups) -
  reference on `GROUP BY (*, entity.field)`