[BUG] Invalid SQL Generation: Nested Aggregations When Combining Filtered Measures with Rolling Windows and Joins

[nandresen-stripe]

Describe the bug
When using rolling windows with filters and joins in Tesseract, invalid SQL is generated that either produces nested aggregations (without multi_stage: true) or incorrect column references (with multi_stage: true).

To Reproduce

1. Create a schema with two cubes (dates and test) joined together
2. Add a filtered measure (revenue)
3. Create a rolling window measure (revenue_ytd) based on the filtered measure
4. Query the revenue_ytd measure
5. Error occurs: either "Cannot nest aggregations inside aggregation 'sum'" or "Column 'test.revenue' cannot be resolved"

Expected behavior
The query should generate valid SQL that correctly calculates the year-to-date revenue while respecting both the filter and join conditions.

Minimally reproducible Cube Schema

cubes:
  - name: dates
    sql: >
      SELECT cast('2024-01-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-02-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-03-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-04-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-05-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-06-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-07-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-08-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-09-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-10-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-11-13' AS timestamp) as time UNION ALL
      SELECT cast('2024-12-13' AS timestamp) as time
    dimensions:
      - name: time
        sql: time
        type: time
        primary_key: true
  - name: test
    sql: >
      SELECT 1 AS revenue,  cast('2024-01-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-02-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-03-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-04-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-05-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-06-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-07-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-08-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-09-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-10-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-11-01' AS timestamp) as time UNION ALL
      SELECT 1 AS revenue,  cast('2024-12-01' AS timestamp) as time

    dimensions:
      - name: time
        sql: time
        type: time
        primary_key: true

    measures:
      - name: revenue
        sql: revenue
        type: sum
        filters:
          - sql: "{dates.time} <= current_date"

      - name: revenue_ytd
        sql: "{CUBE.revenue}"
        type: sum
        rolling_window:
          type: to_date
          granularity: year

    joins:
      - name: dates
        relationship: many_to_one
        sql: "{CUBE}.time = date_trunc('month',{dates.time})"

Version:
v.1.2.3

Additional Context
The workaround for the sum(sum(revenue)) issue is to change the type of revenue to number (and then create a separate measure if we want a sum). However I have not found a workaround for the Column 'test.revenue' cannot be resolved issue for any multi_stage = true measure