This week we’re asking for a combination of different skills and troubleshooting.
We’ve looked at distinct datasets on the Marketplace and we’re wondering if it can help us in the future if we were to combine the sets!
Your challenge for this week :
– Connect to the public dataset “COVID-19 Epidemiological Data” by Starschema (link)
– Connect to the public dataset “Daily Weather Data” by Knoema (link)
– For the last reported date (look for a flag) , for every country in the COVID set , return the maximum value of the Mean temperature (Fahrenheit) from the weather station in that country that has the ‘highest name’. We’re also looking for you to use RANK() to determine that specific weather station.
– Sorted by Country Name in Ascending order
The result that we’re looking for, looks something like this :

We know that we’re asking for a combination of skills in this weeks challenge but don’t be disheartened and work step-by-step!
Remember, if you want to participate:
Happy New Year!
I get some slightly different results, maybe because the dates don’t exactly match. Also, got a lot of nulls as countries / identifiers are different (e.g. Iran / Iran, Islamic Republic of, and couldn’t find a good code-candidate).
Thanks, and to a new year of Frosty Fridays!
oops, the url https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_28.sql
I missed this one when it came out last week. Glad I spotted it today!