Exercise 18 of 19

Swiss counts of common Danish lineages

Fixed training server

Take the Pango lineages with more than 10,000 sequences from Denmark. How many sequences from Switzerland does each of them have? Return the lineages with their Swiss sequence count, highest first.

The output should have this shape:

pangoLineage | count
------------ | -----
B.1.1.7      | 1234
AY.43        | 987

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

Loading query editor…
Explanation

Write a subquery that counts the Danish sequences per lineage, keeps the lineages above 10,000 with a filter after the group, and projects the lineage column. Pass it to in to filter the Swiss sequences to those lineages, then group them by lineage and order the rows by their counts.

Reference answer
data
  .filter(country = 'Switzerland' && pangoLineage.in(
    data
      .filter(country = 'Denmark')
      .group(by := {pangoLineage}, aggs := {count := count()})
      .filter(count > 10000)
      .project({pangoLineage})
  ))
  .group(by := {pangoLineage}, aggs := {count := count()})
  .order(by := {count.desc()})