Exercise 15 of 19

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:

S[69] | S[70] | S[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. Group by the S symbols at positions 69, 70 and 501, written as S[69], S[70] and S[501], and count the sequences for each combination. Order the rows by their counts.

Reference answer
data
  .filter(country = 'Switzerland')
  .group(by := {S[69], S[70], S[501]}, aggs := {count := count()})
  .order(by := {count.desc()})