Weekly granularity and multi-stage calculations

> That looks like a great feature!
>
> Testing it, I have what I think is an unexpected behaviour when I select weekly granularity.
> On a measure summing revenues, the result for `revenue_prior_year` on the 1st week of 2025 does not match the result for `revenue` for the 1st week of 2024.
>
> I can get the expected behaviour if I set
> ```
> - name: revenue_last_year_weekly
> sql: revenue
> type: number
> rolling_window:
> trailing: 52 week
> leading: -51 week
> offset: start
> ```
>
> | date | revenue | revenue_last_year_weekly
> | ------------- | ------------- | ------------- |
> | 2024-01-01 W1 | 409,001 | NA |
> | ... | ... | ... |
> | 2024-12-30 W1 | 230,002 | 409,001 |
>
> But with the recommended implementation of multi-stage calculations, the numbers don't match anymore.
>
> ```
> - name: revenue_prior_year
> multi_stage: true
> sql: "{revenue}"
> type: number
> time_shift:
> - time_dimension: date
> interval: 1 year
> type: prior
> ```
> | date | revenue | revenue_prior_year
> | ------------- | ------------- | ------------- |
> | 2024-01-01 W1 | 409,001 | NA |
> | ... | ... | ... |
> | 2024-12-30 W1 | 230,002 | 558,208 |
>
> That approach returns matching results when the time dimension is set at yearly or monthly level, but not for weekly granularity.
> I could keep the first one but it'd mean to have a dedicated measure for each time dimension granularity which is not ideal.
> By the way, the first approach stops working when setting `cubejs_tesseract_sql_planner = true`. It return the following error:
>
> `SQL compilation error: syntax error line 19 at position 6 unexpected 'VALUES'.`
>
> Am I misinterpreting the expected behaviour? Is there a way we can align comparison vs last year on weekly granularity too?

_Originally posted by @AlexisBocuze in [#8486](https://github.com/cube-js/cube/issues/8486#issuecomment-2851646272)_