[Bug] Time offset doesn't work for nested derived metric

[courtneyholcomb]

Is this a new bug in metricflow?

• I believe this is a new bug in metricflow
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

Currently, if you apply an offset_window or offset_to_grain to the outer metric of a nested derived metric, the offset will not be applied.
Example:

metric:
  name: "bookings_offset_once"
  description: bookings metric offset once.
  type: derived
  type_params:
    expr: 2 * bookings
    metrics:
      - name: bookings
        offset_window: 5 days
---
metric:
  name: "bookings_offset_twice"
  description: bookings metric offset twice.
  type: derived
  type_params:
    expr: 2 * bookings_offset_once
    metrics:
      - name: bookings_offset_once
        offset_window: 5 days

Querying bookings_offset_twice with metric_time__day produces this SQL:

-- Compute Metrics via Expressions
SELECT
  metric_time__day
  , 2 * bookings_offset_once AS bookings_offset_twice
FROM (
  -- Compute Metrics via Expressions
  SELECT
    metric_time__day
    , 2 * bookings AS bookings_offset_once
  FROM (
    -- Join to Time Spine Dataset
    -- Pass Only Elements:
    --   ['bookings', 'metric_time__day']
    -- Aggregate Measures
    -- Compute Metrics via Expressions
    SELECT
      subq_12.ds AS metric_time__day
      , SUM(subq_10.bookings) AS bookings
    FROM ***************************.mf_time_spine subq_12
    INNER JOIN (
      -- Read Elements From Semantic Model 'bookings_source'
      -- Metric Time Dimension 'ds'
      SELECT
        DATE_TRUNC('day', ds) AS metric_time__day
        , 1 AS bookings
      FROM ***************************.fct_bookings bookings_source_src_10001
    ) subq_10
    ON
      subq_12.ds - INTERVAL 5 day = subq_10.metric_time__day
    GROUP BY
      subq_12.ds
  ) subq_16
) subq_17

Above you can see that only one offset is happening, when two would be expected.

Expected Behavior

The SQL should have an offset for the outer metric (bookings_offset_twice):

SELECT
  subq_9.metric_time__day
  , 2 * bookings_offset_once AS bookings_offset_twice
FROM {{ source_schema }}.mf_time_spine subq_9
INNER JOIN (
  SELECT
    metric_time__day
    , 2 * bookings AS bookings_offset_once
  FROM (
    SELECT
      subq_3.ds AS metric_time__day
      , SUM(subq_1.bookings) AS bookings
    FROM {{ source_schema }}.mf_time_spine subq_3
    INNER JOIN (
      SELECT
        DATE_TRUNC('day', ds) AS metric_time__day
        , 1 AS bookings
      FROM {{ source_schema }}.fct_bookings bookings_source_src_1
    ) subq_1
    ON
      subq_3.ds - INTERVAL 5 day = subq_1.metric_time__day
    GROUP BY
      subq_3.ds
  ) subq_7
) subq_8
ON
  subq_9.ds - INTERVAL 5 day = subq_8.metric_time__day

Steps To Reproduce

Add a nested derived metric where the outer metric has an offset, then query it with metric_time.

Relevant log output

No response

Environment

- OS:
- Python:
- dbt:
- metricflow:

Which database are you using?

No response

Additional Context

No response