Exercise 17 of 17

Find Swiss lineages absent from Argentina

Fixed training server

Which Pango lineages were observed in Switzerland but never in Argentina? Return the 20 lineages with the largest Swiss sequence count, highest first.

The output should have this shape:

pangoLineage | countSwitzerland
------------ | ----------------
AY.43.4      | 2776
B.1.177      | 2661

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

Loading query editor…

Explanation

Get a distinct table of lineages for each country using groupBy. Then, use a left anti join to keep the lineages from Switzerland without a match from Argentina. Order the remaining rows by their counts and keep the first 20.

join null checks

Reference answer

default
  .filter(country = 'Switzerland' && isNotNull(pangoLineage))
  .groupBy({countSwitzerland:=count()}, {pangoLineage})
  .join(
    default
      .filter(country = 'Argentina')
      .groupBy({countArgentina:=count()}, {pangoLineage})
      .map({pangoLineageArgentina:=pangoLineage})
      .project({pangoLineageArgentina}),
    pangoLineage = pangoLineageArgentina,
    type := leftAnti
  )
  .orderBy({countSwitzerland.desc()})
  .limit(20)