Exercise 15 of 17

Co-occurring S protein changes

Fixed training server

In the sequences from Switzerland, investigate the co-occurrence patterns at S protein positions 69, 70 and 501. Return one row for each combination together with the count. Order the combinations by count descending so the most common S-position pattern appears first.

The output should have this shape:

pos_69 | pos_70 | pos_501 | count
------ | ------ | ------- | -----
H      | V      | N       | 1234
-      | -      | Y       | 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 sequences from Switzerland. Use map with at to add the S symbols at positions 69, 70 and 501, then use groupBy to count the sequences for each combination. Order the rows by their counts.

at map and groupBy

Reference answer

default
  .filter(country = 'Switzerland')
  .map({pos_69 := S.at(69), pos_70 := S.at(70), pos_501 := S.at(501)})
  .groupBy({count := count()}, {pos_69, pos_70, pos_501})
  .orderBy({count.desc()})