BigQuery sum counts from start of year to date

Question

I Have a table (TEST_TABLE) which gives me the HEADCOUNT on the last day of each month (LAST_DAY_MONTH) for all individual companies (COMPANNY)

an example for 1 company:

LAST_DAY_MONTH	COMPANNY	HEADCOUNT
2023-01-31	x1	20
2023-02-28	x1	22
2023-03-31	x1	21
2023-04-30	x1	27

Is there a possibility to create a new table with a summed headcount to date?

i.e

LAST_DAY_MONTH	COMPANNY	HEADCOUNT_TO_DATE
2023-01-31	x1	20
2023-02-28	x1	42
2023-03-31	x1	63
2023-04-30	x1	90

I allready tried

SELECT 
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(CASE WHEN LAST_DAY_MONTH BETWEEN DATE_TRUNC(LAST_DAY_MONTH, year) AND LAST_DAY_MONTH THEN HEADCOUNT END)
FROM `TEST_TABLE`
GROUP BY 1,2

But this just gives the headcount of each month

EDIT: I ony want to sum headcounts from the same year so if I would look at the row of 2022-12-31, the HEADCOUNT_TO_DATE should be the sum of all 12 months of 2022.

On 2023-01-31 it shoud then only sum the headcount of the first month of 2023

Answer 1

I found the solution, the answer of Asi Salo pointed me in the right direction:

SELECT 
LAST_DAY_MONTH,
COMPANY_NAME,
SUM(HEADCOUNT) OVER(PARTITION BY COMPANY_NAME, EXTRACT(year FROM LAST_DAY_MONTH) ORDER BY EXTRACT(month FROM LAST_DAY_MONTH)) AS HEADCOUNT_TO_DATE
FROM `TEST_TABLE`