with data_21 as (
select * from (
select country_region, date as date_21, confirmed as confirmed_21, deaths as deaths_21
,LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day_21
,confirmed - LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) as new_cases_21
from `bigquery-public-data.covid19_jhu_csse.summary`
WHERE upper(country_region) = 'HUNGARY' AND date between '2020-01-01'
AND (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`)
)),

MONTHLY_NEW_CASES AS (
select
left(cast(date_21 as string),7) as yearmonth
,sum(new_cases_21) as res
from data_21
group by 1
order by 1 asc
),

TOTAL_CASES_MONTHS AS (
SELECT
left(cast(date_21 as string),7) as yearmonth
,MAX(confirmed_21) as res
from data_21
group by 1
order by 1 asc
)

SELECT * FROM TOTAL_CASES_MONTHS