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      | 987

Write a query and run it. The result is compared with the reference answer without considering row order.

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()})