selecting the value of a column that correspond to the last date of a selected date range in Bigquery

Question

I have the following query and i want to fetch the last date of the interval date queried the accumulated_value corresponding to the last date within that interval and then the sum of daily_growth for the whole interval: I couldn't figure out what is wrong with the query.

SELECT
  max(date) as last_date,
  accumulated_value_last_date,
  sum(sum_daily_growth_interval)
FROM (
  SELECT
    date,
    LAST_VALUE(accumulated_value IGNORE NULLS) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS accumulated_value_last_date,
    daily_growth AS sum_daily_growth_interval
  FROM
    `my_table`
  WHERE
    date BETWEEN '2023-12-18' AND '2023-12-20'
)
group by accumulated_value_last_date;

here is an example of my_table:

This is the expected result:

But this is what i got:

Answer 1

The last accumulated_value can be calculated by using any_value and the having max condition.

SELECT
  max(date) as last_date,
  any_value(accumulated_value having max date) as last_accumulated_value,
  sum(daily_growth) as growth
  FROM
    `my_table`
  WHERE
    date BETWEEN '2023-12-18' AND '2023-12-20'