[SL-1357] [Bug] Time constraint should be removed in metric subquery for time offset metrics

[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

In time offset queries, the time constraint is applied to both the time spine subquery and the metric subquery. Here's the current SQL:

-- Compute Metrics via Expressions
SELECT
  metric_time__day
  , bookings_2_weeks_ago AS bookings_2_weeks_ago
FROM (
  -- Join to Time Spine Dataset
  -- Pass Only Elements:
  --   ['bookings', 'metric_time__day']
  -- Aggregate Measures
  -- Compute Metrics via Expressions
  SELECT
    subq_12.metric_time__day AS metric_time__day
    , SUM(subq_11.bookings) AS bookings_2_weeks_ago
  FROM (
    -- Date Spine
    SELECT
      ds AS metric_time__day
    FROM ***************************.mf_time_spine subq_13
    WHERE ds BETWEEN '2020-01-01' AND '2020-01-01'
  ) subq_12
  INNER JOIN (
    -- Read Elements From Semantic Model 'bookings_source'
    -- Metric Time Dimension 'ds'
    -- Constrain Time Range to [2020-01-01T00:00:00, 2020-01-01T00:00:00]
    SELECT
      DATE_TRUNC('day', ds) AS metric_time__day
      , 1 AS bookings
    FROM ***************************.fct_bookings bookings_source_src_10001
    WHERE DATE_TRUNC('day', ds) BETWEEN '2020-01-01' AND '2020-01-01'
  ) subq_11
  ON
    subq_12.metric_time__day - INTERVAL 14 day = subq_11.metric_time__day
  GROUP BY
    subq_12.metric_time__day
) subq_17

Expected Behavior

Since the metric dates will be offset, the time constraint should not be applied in that subquery. The inner join to the time spine query will effectively apply that time constraint, instead.

-- Compute Metrics via Expressions
SELECT
  metric_time__day
  , bookings_2_weeks_ago AS bookings_2_weeks_ago
FROM (
  -- Join to Time Spine Dataset
  -- Pass Only Elements:
  --   ['bookings', 'metric_time__day']
  -- Aggregate Measures
  -- Compute Metrics via Expressions
  SELECT
    subq_12.metric_time__day AS metric_time__day
    , SUM(subq_11.bookings) AS bookings_2_weeks_ago
  FROM (
    -- Date Spine
    SELECT
      ds AS metric_time__day
    FROM ***************************.mf_time_spine subq_13
    WHERE ds BETWEEN '2020-01-01' AND '2020-01-01'
  ) subq_12
  INNER JOIN (
    -- Read Elements From Semantic Model 'bookings_source'
    -- Metric Time Dimension 'ds'
    -- Constrain Time Range to [2020-01-01T00:00:00, 2020-01-01T00:00:00]
    SELECT
      DATE_TRUNC('day', ds) AS metric_time__day
      , 1 AS bookings
    FROM ***************************.fct_bookings bookings_source_src_10001
  ) subq_11
  ON
    subq_12.metric_time__day - INTERVAL 14 day = subq_11.metric_time__day
  GROUP BY
    subq_12.metric_time__day
) subq_17

Steps To Reproduce

Query a time offset metric with a time constraint.

Relevant log output

No response

Environment

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

Which database are you using?

No response

Additional Context

No response

_SL-1357

[courtneyholcomb]

test case here https://github.com/dbt-labs/metricflow/pull/924/files#diff-51acb2b23df205089dc710694c7d7f83d37eb697e1e3190ebe8fb93a6a7bae59R29