Archives for January 2023

Frosty Friday’s super academy wants to know who their best and worst super heroes are, and the only metric that matters is villains defeated. Using MIN_BY and MAX_BY, tell us which super hero is the best, and which is the worst.

create or replace table w31(id int, hero_name string, villains_defeated number);

insert into w31 values
  (1, 'Pigman', 5),
  (2, 'The OX', 10),
  (3, 'Zaranine', 4),
  (4, 'Frostus', 8),
  (5, 'Fridayus', 1),
  (6, 'SheFrost', 13),
  (7, 'Dezzin', 2.3),
  (8, 'Orn', 7),   
  (9, 'Killder', 6),   
  (10, 'PolarBeast', 11)
  ;

Your end result should look something like this:

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge

This week, we’ll be looking into a challenge that will make any salesperson salivate :

The instant “database-in-a-box” or a general-use template:
We want to be able to use SnowSQL to point it towards this script on the 1 end, and the target snowflake instance on the other, and create an instant useable database.

Create a Primary Script (in a format of your choice), that creates the following if executed :
– 4 databases, named: Development, Testing, Acceptance, and Production
– 1 additional schema in every database (besides Public) called security
– 1 additional schema in Development called ‘dev_user’
– 3 users: security_user, dev_user, regular_user

The default values for the users :
password = abc123
secondary roles should be enabled

– 2 additional roles called dev_role and security_role
– dev_role should be able to create and query all (future) tables, views, and schemas in every database, except for changing anything in the security schemas.
– security_role should be able to create and query all (future) tables, views, and schemas in every database.
– dev_user should have the dev_role , security_user should have the security_role
– the role public is only allowed to query Production.Public
– 1 SMALL warehouse called default_wh

Create a secondary script that has the following :

The database-in-a-box should call on this Secondary Script if you point it to the right path (argument).
The path to the Secondary Script should NOT be hardcoded in the Primary Script

BONUS POINTS :

As a bonus challenge, try to create the same script that is more malleable ;
The default values like passwords, warehouse names, and database names are currently static but, can you create the option to defer to the default if no alternative is given?

Remember, if you want to participate:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge

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:

1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
3. Post the URL in the comments of the challenge