with check_countries as (
SELECT count(distinct country_region)
FROM `bigquery-public-data.covid19_jhu_csse.summary` LIMIT 1000
),

India_filter as (
select
country_region,
date,
sum(confirmed) as confirmed,
sum(deaths) as deaths,
sum(recovered) as recovered,
sum(active) as active
FROM `bigquery-public-data.covid19_jhu_csse.summary`
where upper(country_region) = 'INDIA'
GROUP BY 1,2
),

filtered as (
select date, confirmed, deaths, recovered, active
, LAG(confirmed, 1) OVER (PARTITION BY country_region ORDER BY date ASC) AS prev_day
from India_filter
WHERE date between '2021-02-01' and (select max(date) from `bigquery-public-data.covid19_jhu_csse.summary`)
),

usual_tbl as (
select *,
confirmed - prev_day as new_cases
--,round(1-(deaths/confirmed),4) as survival_rate
from filtered
order by date asc
),

population_est as (
--now for some context based on population:
select *, 1380000000 as Total_population_estimate
from usual_tbl
)

select *,
round((confirmed/Total_population_estimate) * 100,2) as infected_rate
from population_est