with check_countries as (
SELECT count(distinct country_region)
FROM `bigquery-public-data.covid19_jhu_csse.summary` LIMIT 1000
),
Hu_filter as (
select 'HU' as country, * from `bigquery-public-data.covid19_jhu_csse.summary`
where upper(country_region) = 'HUNGARY'
),
filtered as (
select date, confirmed, deaths, recovered, active
, LAG(confirmed, 1) OVER (PARTITION BY country ORDER BY date ASC) AS prev_day
from Hu_filter
WHERE date between '2021-03-01' and (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`)
)
select *,
confirmed - prev_day as new_cases
--,round(1-(deaths/confirmed),4) as survival_rate
from filtered
where confirmed - prev_day is not null
order by date asc