Exercise 4 of 17

Compare worldwide and Spanish lineage counts

Fixed training server

Identify the most common 50 pango lineages worldwide and return their worldwide sequence count and their sequence count from Spain. Sort by the worldwide count, highest first.

The output should have this shape:

pangoLineage | countWorld | countSpain
------------ | ---------- | ----------
BA.2         | 1234       | 42
XBB          | 987        | null

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

Loading query editor…

Explanation

Get a table of worldwide sequence counts for each lineage using groupBy. Build the same table for Spain and rename its lineage column, then use a left join to retain worldwide lineages without a match from Spain. Project the requested columns, order the rows by their worldwide counts and keep the first 50.

join groupBy

Reference answer

default
  .groupBy({countWorld:=count()}, {pangoLineage})
  .join(
    default
      .filter(country = 'Spain')
      .groupBy({countSpain:=count()}, {pangoLineage})
      .map({pangoLineage2 := pangoLineage})
      .project({pangoLineage2, countSpain}),
    pangoLineage = pangoLineage2,
    type := left
  )
  .project({pangoLineage, countWorld, countSpain})
  .orderBy({countWorld.desc()})
  .limit(50)