January 2023

by Christopher Marland on 2023-01-27 5 Comments

Week 31 – Basic

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.

Start-Up Code

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:

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

by Mike Droog on 2023-01-20 Leave a Comment

Week 30 – Intermediate

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 :

Secondary script

CREATE TABLE departments(dep_id varchar, dep_name varchar);

INSERT INTO departments VALUES
(‘d001′,’Marketing’),
(‘d002′,’Finance’),
(‘d003′,’Human Resources’),
(‘d004′,’Production’),
(‘d005′,’Development’),
(‘d006′,’Quality Management’),
(‘d007′,’Sales’),
(‘d008′,’Research’),
(‘d009′,’Customer Service’);

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:

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

by Christopher Marland on 2023-01-13 2 Comments

Week 29 – Intermediate

Snowpark skills are going to be seen as more and more useful as time goes by, and for this reason, we’re posting another Snowpark challenge!

This week, we’re registering UDFs.

Your start-up code is below – it will create a table for you that has employees and their start dates.

Start-Up Code

create or replace file format frosty_csv
    type = csv
    skip_header = 1
    field_optionally_enclosed_by = '"';

create stage w29_stage
    url = 's3://frostyfridaychallenges/challenge_29/'
    file_format = frosty_csv;
    
list @w29_stage;
    
create table week29 as     
select t.$1::int as id, 
        t.$2::varchar(100) as first_name, 
        t.$3::varchar(100) as surname, 
        t.$4::varchar(250) as email, 
        t.$5::datetime as start_date 
from @w29_stage (pattern=>'.*start_dates.*') t;

You need to create a UDF that will produce a fiscal year for the start_date. The logic should be that if the month is from May onwards* then the fiscal year should be the current year + 1, otherwise, the current year.

For example:

2022-05-13 = FY23

2022-02-11=FY22

Once you’ve written your UDF, execute the following code:

data = session.table("week29").select(
    col("id"),
    col("first_name"),
    col("surname"),
    col("email"),
    col("start_date"),
    fiscal_year("start_date").alias("fiscal_year")
)

data.show()

and

data.group_by("fiscal_year").agg(col("*"), "count").show()

The result should look like, this:

And there you have it!

*because we said so.

by Mike Droog on 2023-01-06 3 Comments

Week 28 – Intermediate

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:

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

Archives for January 2023