Skip to content

Instantly share code, notes, and snippets.

@djouallah

djouallah/parameter query

Last active August 31, 2020 23:21
Show Gist options
  • Save djouallah/2a33af59d23e3ccd065a2d1f8d707958 to your computer and use it in GitHub Desktop.
Save djouallah/2a33af59d23e3ccd065a2d1f8d707958 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
parameter query
with tt as (SELECT gg.countries_and_territories as Countries , date, sum( daily_deaths ) as daily_deaths ,max(pop) as pop FROM `GIS.covid19new` gg
left join (SELECT countries_and_territories,max( pop_data_2019) as pop FROM `GIS.covid19new` group by 1) pp
on gg.countries_and_territories =pp.countries_and_territories
where gg.countries_and_territories in unnest(@selection) group by 1,2
UNION ALL
SELECT "Rest of the World" as Countries , date,sum( daily_deaths ) as daily_deaths,sum(pop ) FROM `GIS.covid19new` gg
left join (SELECT countries_and_territories,max( pop_data_2019) as pop FROM `GIS.covid19new` group by 1) pp
on gg.countries_and_territories =pp.countries_and_territories
where gg.countries_and_territories not in unnest(@selection) group by 1,2)
select *, sum(daily_deaths) over(partition by Countries order by date) as daily_deaths_cumulative from tt
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment