Calculate working hours between exit date and 3 months before exit date bigquery sql

I’m trying to calculate the total working hours between two dates in bigquery sql: The dates being between MAX(date) and DATE_SUB(MAX(date), interval 3 month). In other words, I want to know the sum of working hours between the exit date and 3 months prior to the exit date.

The current table is something like this:

id	date	hours
abc	2020-10-01	12
abc	2020-12-07	4
abc	2020-12-12	12
abc	2020-12-25	6
abc	2021-01-07	9
abc	2021-02-04	7

The ideal output is:

id	hours
abc	38

I have multiple workers and workers have different working dates and hours.

Answer

We need a subquery here to calculate exit_date first:

with mytable as (
  select 'abc' as id, DATE '2020-10-01' as date, 12 as hours union all
  select 'abc' as id, DATE '2020-12-07' as date, 4 as hours union all
  select 'abc' as id, DATE '2020-12-12' as date, 12 as hours union all
  select 'abc' as id, DATE '2020-12-25' as date, 6 as hours union all
  select 'abc' as id, DATE '2021-01-07' as date, 9 as hours union all
  select 'abc' as id, DATE '2021-02-04' as date, 7 as hours
)
select 
  id,
  sum(hours) as hours
from (
  select *, MAX(date) OVER (PARTITION BY id) as exit_date
  from mytable
)
where date >= DATE_SUB(exit_date, INTERVAL 3 MONTH)
group by id