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 groupBy to count the sequences for each lineage, order the rows by their counts and keep the first 20.

between groupBy and orderBy

Reference answer

default
  .filter(date.between('2021-01-01'::date, '2021-06-30'::date))
  .groupBy({count:=count()}, {pangoLineage})
  .orderBy({count.desc()})
  .limit(20)