Exercise 11 of 19
Filter with a set of values
Fixed training server
For the sequences from Germany, France and Italy, return per country the number of sequences, the number of sequences with a collection date, and the earliest and latest collection date. Order the countries by their number of sequences, highest first.
The output should have this shape:
country | count | withDate | first | last
------- | ----- | -------- | ---------- | ----------
Germany | 1234 | 1200 | 2020-01-28 | 2024-10-08
France | 987 | 700 | 2020-02-27 | 2024-01-04Write a query and run it. The result is compared with the reference answer without considering row order.
Loading query editor…
Loading query editor…
Explanation
Filter the table with in to keep sequences from Germany, France and Italy. Then, use group to compute four aggregates per country: count() counts all rows, count(date) only the rows with a date, and min(date) and max(date) find the earliest and latest date. Order the rows by their counts.
Reference answer
data
.filter(country.in({'Germany', 'France', 'Italy'}))
.group(by:={country}, aggs:={count:=count(), withDate:=count(date), first:=min(date), last:=max(date)})
.order(by:={count.desc()})