Exercise 16 of 17

Compare recent German and US submissions

Fixed training server

Build one harmonized table for recent SARS-CoV-2 submissions from Germany and the USA. Include the strain name, collection date, pango lineage and a place column.

For Germany, place should identify the country (i.e. always just be "Germany"). For the USA, place should identify the division. Use the 100 most recent German rows and the 100 most recent US rows, then sort the combined 200-row table by date descending.

The output should have this shape:

strain        | date       | pangoLineage | place
------------- | ---------- | ------------ | ----------
sample-DE-001 | 2024-05-10 | JN.1         | Germany
sample-US-001 | 2024-05-09 | JN.1.4       | California

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

Loading query editor…

Explanation

Build a table of the 100 most recent German sequences and use map to copy the country into the place column. Build the same table for the USA with the division in the place column, then combine both tables with unionAll. Order the combined rows by date descending.

Pipeline schemas unionAll

Reference answer

default
  .filter(country = 'Germany')
  .map({place := country})
  .project({strain, date, pangoLineage, place})
  .orderBy({date.desc()})
  .limit(100)
  .unionAll(
    default
      .filter(country = 'USA')
      .map({place := division})
      .project({strain, date, pangoLineage, place})
      .orderBy({date.desc()})
      .limit(100)
  )
  .orderBy({date.desc()})