Exercise 3 of 19
Lineage counts in early 2021
Fixed training server
For sequences collected between 1 January 2021 and 30 June 2021, count the sequences per pangoLineage and return every lineage with at least 10,000 sequences, most frequent first.
The output should have this shape:
pangoLineage | count
------------ | -----
B.1.1.7 | 1234
B.1.351 | 987Write a query and run it. The result is compared with the reference answer without considering row order.
Loading query editor…
Loading query editor…
Explanation
Filter the table to dates between 1 January and 30 June 2021. Then, use group to count the sequences for each lineage, filter the grouped rows to counts of at least 10,000 and order them by their counts.
Reference answer
data
.filter(date.between('2021-01-01'::date, '2021-06-30'::date))
.group(by:={pangoLineage}, aggs:={count:=count()})
.filter(count >= 10000)
.order(by:={count.desc()})