COVID-19 Flux Queries

confirmed-cases-since-100th.flux

alignTime = (tables=<-, alignTo=time(v: 0)) => {
  _tableInfo = tables |> tableFind(fn: (key) => true) |> getRecord(idx: 0)
  _startTime = if alignTo != time(v: 0) then alignTo else if exists _tableInfo._start then _tableInfo._start else time(v: 0)
  _data = tables
    |> stateDuration(
      fn: (r) => true,
      column: "timeDiff",
      unit: 1ns
    )
    |> map(fn: (r) => ({ r with _time: time(v: (int(v: _startTime ) + r.timeDiff))}))
    |> drop(columns: ["timeDiff"])
  return _data
}

from(bucket: "corona")
  |> range(start: 2019-12-30T00:00:00Z)
  |> filter(fn: (r) => r.country != "World" and r._field == "total_cases")
  |> filter(fn: (r) => r._value >= 100)
  |> alignTime()
  |> map(fn: (r) => ({ r with day: 1}))
  |> cumulativeSum(columns: ["day"])

deaths-since-first-death.flux

alignTime = (tables=<-, alignTo=time(v: 0)) => {
  _tableInfo = tables |> tableFind(fn: (key) => true) |> getRecord(idx: 0)
  _startTime = if alignTo != time(v: 0) then alignTo else if exists _tableInfo._start then _tableInfo._start else time(v: 0)
  _data = tables
    |> stateDuration(
      fn: (r) => true,
      column: "timeDiff",
      unit: 1ns
    )
    |> map(fn: (r) => ({ r with _time: time(v: (int(v: _startTime ) + r.timeDiff))}))
    |> drop(columns: ["timeDiff"])
  return _data
}

from(bucket: "corona")
  |> range(start: 2019-12-30T00:00:00Z)
  |> filter(fn: (r) => r.country != "World" and r._field == "total_deaths")
  |> alignTime()
  |> map(fn: (r) => ({ r with day: 1}))
  |> cumulativeSum(columns: ["day"])

top-10-cases-per-100k.flux

import "math"
import "csv"

csvData = "
#datatype,string,long,string,long
#group,false,true,false,false
#default,,,,
,result,table,country,population
,,0,Aruba,105845
,,0,Afghanistan,37172386
,,0,Angola,30809762
,,0,Albania,2866376
,,0,Andorra,77006
,,0,Arab World,419790588
,,0,United Arab Emirates,9630959
,,0,Argentina,44494502
,,0,Armenia,2951776
,,0,American Samoa,55465
,,0,Antigua and Barbuda,96286
,,0,Australia,24992369
,,0,Austria,8847037
,,0,Azerbaijan,9942334
,,0,Burundi,11175378
,,0,Belgium,11422068
,,0,Benin,11485048
,,0,Burkina Faso,19751535
,,0,Bangladesh,161356039
,,0,Bulgaria,7024216
,,0,Bahrain,1569439
,,0,Bahamas,385640
,,0,Bosnia and Herzegovina,3323929
,,0,Belarus,9485386
,,0,Belize,383071
,,0,Bermuda,63968
,,0,Bolivia,11353142
,,0,Brazil,209469333
,,0,Barbados,286641
,,0,Brunei Darussalam,428962
,,0,Bhutan,754394
,,0,Botswana,2254126
,,0,Central African Republic,4666377
,,0,Canada,37058856
,,0,Central Europe and the Baltics,102511922
,,0,Switzerland,8516543
,,0,Channel Islands,170499
,,0,Chile,18729160
,,0,China,1392730000
,,0,Cote d'Ivoire,25069229
,,0,Cameroon,25216237
,,0,Democratic Republic of Congo,84068091
,,0,Republic of Congo,5244363
,,0,Colombia,49648685
,,0,Comoros,832322
,,0,Cabo Verde,543767
,,0,Costa Rica,4999441
,,0,Cuba,11338138
,,0,Curacao,159849
,,0,Cayman Islands,64174
,,0,Cyprus,1189265
,,0,Czech Republic,10625695
,,0,Germany,82927922
,,0,Djibouti,958920
,,0,Dominica,71625
,,0,Denmark,5797446
,,0,Dominican Republic,10627165
,,0,Algeria,42228429
,,0,East Asia & Pacific,2328220870
,,0,Europe & Central Asia,918793590
,,0,Ecuador,17084357
,,0,Egypt,98423595
,,0,Spain,46723749
,,0,Estonia,1320884
,,0,Ethiopia,109224559
,,0,European Union,513213363
,,0,Finland,5518050
,,0,Fiji,883483
,,0,France,66987244
,,0,Faroe Islands,48497
,,0,Micronesia,112640
,,0,Gabon,2119275
,,0,United Kingdom,66488991
,,0,Georgia,3731000
,,0,Ghana,29767108
,,0,Gibraltar,33718
,,0,Guinea,12414318
,,0,Gambia,2280102
,,0,Guinea-Bissau,1874309
,,0,Equatorial Guinea,1308974
,,0,Greece,10727668
,,0,Grenada,111454
,,0,Greenland,56025
,,0,Guatemala,17247807
,,0,Guam,165768
,,0,Guyana,779004
,,0,High income,1210312147
,,0,Hong Kong,7451000
,,0,Honduras,9587522
,,0,Heavily indebted poor countries (HIPC),780234406
,,0,Croatia,4089400
,,0,Haiti,11123176
,,0,Hungary,9768785
,,0,IBRD only,4772284113
,,0,IDA & IBRD total,6412522234
,,0,IDA total,1640238121
,,0,IDA blend,555830605
,,0,Indonesia,267663435
,,0,IDA only,1084407516
,,0,Isle of Man,84077
,,0,India,1352617328
,,0,Not classified,
,,0,Ireland,4853506
,,0,Iran,81800269
,,0,Iraq,38433600
,,0,Iceland,353574
,,0,Israel,8883800
,,0,Italy,60431283
,,0,Jamaica,2934855
,,0,Jordan,9956011
,,0,Japan,126529100
,,0,Kazakhstan,18276499
,,0,Kenya,51393010
,,0,Kyrgyz Republic,6315800
,,0,Cambodia,16249798
,,0,Kiribati,115847
,,0,St. Kitts and Nevis,52441
,,0,South Korea,51635256
,,0,Kuwait,4137309
,,0,Latin America & Caribbean (excluding high income),609013934
,,0,Lao PDR,7061507
,,0,Lebanon,6848925
,,0,Liberia,4818977
,,0,Libya,6678567
,,0,St. Lucia,181889
,,0,Latin America & Caribbean,641357515
,,0,Least developed countries: UN classification,1009662578
,,0,Low income,705417321
,,0,Liechtenstein,37910
,,0,Sri Lanka,21670000
,,0,Lower middle income,3022905169
,,0,Low & middle income,6383958209
,,0,Lesotho,2108132
,,0,Late-demographic dividend,2288665963
,,0,Lithuania,2789533
,,0,Luxembourg,607728
,,0,Latvia,1926542
,,0,St. Martin (French part),37264
,,0,Morocco,36029138
,,0,Monaco,38682
,,0,Moldova,3545883
,,0,Madagascar,26262368
,,0,Maldives,515696
,,0,Middle East & North Africa,448912859
,,0,Mexico,126190788
,,0,Marshall Islands,58413
,,0,Middle income,5678540888
,,0,North Macedonia,2082958
,,0,Mali,19077690
,,0,Malta,483530
,,0,Myanmar,53708395
,,0,Middle East & North Africa (excluding high income),382896715
,,0,Montenegro,622345
,,0,Mongolia,3170208
,,0,Northern Mariana Islands,56882
,,0,Mozambique,29495962
,,0,Mauritania,4403319
,,0,Mauritius,1265303
,,0,Malawi,18143315
,,0,Malaysia,31528585
,,0,North America,364290258
,,0,Namibia,2448255
,,0,New Caledonia,284060
,,0,Niger,22442948
,,0,Nigeria,195874740
,,0,Nicaragua,6465513
,,0,Netherlands,17231017
,,0,Norway,5314336
,,0,Nepal,28087871
,,0,Nauru,12704
,,0,New Zealand,4885500
,,0,OECD members,1303529456
,,0,Oman,4829483
,,0,Other small states,30758989
,,0,Pakistan,212215030
,,0,Panama,4176873
,,0,Peru,31989256
,,0,Philippines,106651922
,,0,Palau,17907
,,0,Papua New Guinea,8606316
,,0,Poland,37978548
,,0,Pre-demographic dividend,919485393
,,0,Puerto Rico,3195153
,,0,North Korea,25549819
,,0,Portugal,10281762
,,0,Paraguay,6956071
,,0,West Bank and Gaza,4569087
,,0,Pacific island small states,2457367
,,0,Post-demographic dividend,1109997273
,,0,French Polynesia,277679
,,0,Qatar,2781677
,,0,Romania,19473936
,,0,Russian Federation,144478050
,,0,Rwanda,12301939
,,0,South Asia,1814388744
,,0,Saudi Arabia,33699947
,,0,Sudan,41801533
,,0,Senegal,15854360
,,0,Singapore,5638676
,,0,Solomon Islands,652858
,,0,Sierra Leone,7650154
,,0,El Salvador,6420744
,,0,San Marino,33785
,,0,Somalia,15008154
,,0,Serbia,6982084
,,0,Sub-Saharan Africa (excluding high income),1078209758
,,0,South Sudan,10975920
,,0,Sub-Saharan Africa,1078306520
,,0,Small states,40575321
,,0,Sao Tome and Principe,211028
,,0,Suriname,575991
,,0,Slovak Republic,5447011
,,0,Slovenia,2067372
,,0,Sweden,10183175
,,0,Eswatini,1136191
,,0,Sint Maarten (Dutch part),40654
,,0,Seychelles,96762
,,0,Syrian Arab Republic,16906283
,,0,Turks and Caicos Islands,37665
,,0,Chad,15477751
,,0,East Asia & Pacific (IDA & IBRD countries),2056064424
,,0,Europe & Central Asia (IDA & IBRD countries),459865205
,,0,Togo,7889094
,,0,Thailand,69428524
,,0,Tajikistan,9100837
,,0,Turkmenistan,5850908
,,0,Latin America & the Caribbean (IDA & IBRD countries),625569713
,,0,Timor-Leste,1267972
,,0,Middle East & North Africa (IDA & IBRD countries),378327628
,,0,Tonga,103197
,,0,South Asia (IDA & IBRD),1814388744
,,0,Sub-Saharan Africa (IDA & IBRD countries),1078306520
,,0,Trinidad and Tobago,1389858
,,0,Tunisia,11565204
,,0,Turkey,82319724
,,0,Tuvalu,11508
,,0,Tanzania,56318348
,,0,Uganda,42723139
,,0,Ukraine,44622516
,,0,Upper middle income,2655635719
,,0,Uruguay,3449299
,,0,United States,327167434
,,0,Uzbekistan,32955400
,,0,St. Vincent and the Grenadines,110210
,,0,Venezuela,28870195
,,0,British Virgin Islands,29802
,,0,Virgin Islands (U.S.),106977
,,0,Vietnam,95540395
,,0,Vanuatu,292680
,,0,World,7594270356
,,0,Samoa,196130
,,0,Kosovo,1845300
,,0,Yemen,28498687
,,0,South Africa,57779622
,,0,Zambia,17351822
,,0,Zimbabwe,14439018
"

total_cases = from(bucket: "corona")
  |> range(start: 2019-12-30T00:00:00Z)
  |> filter(fn: (r) => r.country != "World" and r._field == "total_cases")

population = csv.from(csv: csvData)

data = join(tables: {c: total_cases, p: population}, on: ["country"])
  |> map(fn: (r) => ({ r with _value: math.round(x: (float(v:r._value) / float(v: r.population)) * 100000.0)}))

top10 = total_cases
  |> filter(fn: (r) => r.country != "World" and r._field == "total_cases")
  |> last()
  |> group()
  |> top(n:10)
  |> tableFind(fn: (key) => true)
  |> getColumn(column: "country")

data
  |> filter(fn: (r) => contains(value: r.country, set: top10))