Exercise 3 of 17

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 the 20 most frequent lineages, 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, order the rows by their counts and keep the first 20.

between group and order

Reference answer

data
  .filter(date.between('2021-01-01'::date, '2021-06-30'::date))
  .group(by:={pangoLineage}, aggs:={count:=count()})
  .order(by:={count.desc()})
  .limit(20)