September 2022

by Christopher Marland on 2022-09-30 9 Comments

Week 16 – Intermediate

Tis Friday and what a wonderful day for flexing those JSON-parsing muscles!

Below we have the set up script:

Set Up Code

create or replace file format json_ff
    type = json
    strip_outer_array = TRUE;
    
create or replace stage week_16_frosty_stage
    url = 's3://frostyfridaychallenges/challenge_16/'
    file_format = json_ff;

create or replace table <schema>.week16 as
select t.$1:word::text word, t.$1:url::text url, t.$1:definition::variant definition  
from @week_16_frosty_stage (file_format => 'json_ff', pattern=>'.*week16.*') t;

END RESULT

And below is what you should end up with if you were to run the following:

select *
from (
    <your query goes here>
) sub
where word like 'l%';

If correct, then (without the “where word like ‘l%'” filter) :

count(word) should get you 32,295 rows
count(distinct word) should get you 3,000 rows

DATA SOURCES:

https://www.ef.co.uk/english-resources/english-vocabulary/top-3000-words/
https://dictionaryapi.dev/

BONUS POINTS

If you’re lucky enough to be in one of the following regions, then try and apply search optimization on your table using a variant path.

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 2022-09-23 5 Comments

This week we’re working with small dataset about house sales that needs to be categorized according to certain sliding bins. The catch however , is that the sizes and the number of bins can change quickly.

The challenge is to create a function with a single name that does the following:

can handle uneven bin sizes
the first parameter must be the column that will inform your bins (in this example, we categorise according to [price])
the second parameter should specify the ranges of your bins (remember, these are uneven bins, bin 1 could be 1 – 400, and bin 2 401 – 708, while bin 3 is 709 – 3000) how you do this is up to you: you can specify lower bounds, upper bounds, both, count within each bin….
if using SQL, as a minimum, it should be able to handle 2-6 bins, if using other languages then you will find them flexible enough to allow you to do any number of bins

The query should look like the following :

SELECT sale_date,
       price,
       your_function(price,<bin_ranges>) AS BUCKET_SET1,
       your_function(price,<bin_ranges>) AS BUCKET_SET2,
       your_function(price,<bin_ranges>) AS BUCKET_SET3,
FROM home_sales

You will then need to pass in the following bin/bucket ranges for testing purposes:

Bucket_Set1:
- 1: 0 – 1
- 2: 2 – 310,000
- 3: 310001 – 400000
- 4: 400001 – 500000
Bucket_Set2:
- 1: 0 – 210000
- 2: 210001 – 350000
Bucket_Set3:
- 1: 0 – 250000
- 2: 250001 – 290001
- 3: 290002 – 320000
- 4: 320001 – 360000
- 5: 360001 – 410000
- 6: 410001 – 470001

You will find your setup code here

create table home_sales (
sale_date date,
price number(11, 2)
);

insert into home_sales (sale_date, price) values
(‘2013-08-01’::date, 290000.00),
(‘2014-02-01’::date, 320000.00),
(‘2015-04-01’::date, 399999.99),
(‘2016-04-01’::date, 400000.00),
(‘2017-04-01’::date, 470000.00),
(‘2018-04-01’::date, 510000.00);

Given the buckets above, the following results should be generated:

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‘ (note joining our mailing list does not give you a Frosty Friday account)
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.

You can also sign up to our mailing list below.

by Christopher Marland on 2022-09-16 10 Comments

Week 14 – Basic

This week we’re going to be undoing what people usually do and we’re going to be turning a table into a JSON VARIANT object.

Here we have a table with info on a set of superheroes, your job is to turn this table into a JSON VARIANT object.

Here’s the start-up code

CREATE OR REPLACE TABLE week_14 (
    superhero_name varchar(50),
    country_of_residence varchar(50),
    notable_exploits varchar(150),
    superpower varchar(100),
    second_superpower varchar(100),
    third_superpower varchar(100)
);

INSERT INTO week_14 VALUES ('Superpig', 'Ireland', 'Saved head of Irish Farmer\'s Association from terrorist cell', 'Super-Oinks', NULL, NULL);
INSERT INTO week_14 VALUES ('Señor Mediocre', 'Mexico', 'Defeated corrupt convention of fruit lobbyists by telling anecdote that lasted 33 hours, with 16 tangents that lead to 17 resignations from the board', 'Public speaking', 'Stamp collecting', 'Laser vision');
INSERT INTO week_14 VALUES ('The CLAW', 'USA', 'Horrifically violent duel to the death with mass murdering super villain accidentally created art installation last valued at $14,450,000 by Sotheby\'s', 'Back scratching', 'Extendable arms', NULL);
INSERT INTO week_14 VALUES ('Il Segreto', 'Italy', NULL, NULL, NULL, NULL);
INSERT INTO week_14 VALUES ('Frosty Man', 'UK', 'Rescued a delegation of data engineers from a DevOps conference', 'Knows, by memory, 15 definitions of an obscure codex known as "the data mesh"', 'can copy and paste from StackOverflow with the blink of an eye', NULL);

RESULT

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‘ (note joining our mailing list does not give you a Frosty Friday account)
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 2022-09-09 10 Comments

Week 13 – Basic Snowflake / Intermediate Non-Snowflake

This week we’ve got a bit of a deceptive problem that’s very easy to understand but tricky to execute in SQL.

You will find your setup code here

create or replace table testing_data(id int autoincrement start 1 increment 1, product string, stock_amount int,date_of_check date);
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',1,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',2,'2022-01-02');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',NULL,'2022-02-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero capes',NULL,'2022-03-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',5,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-13');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',6,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',NULL,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',3,'2022-04-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',2,'2022-07-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',NULL,'2022-01-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',3,'2022-05-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero pants',NULL,'2022-10-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',10,'2022-11-01');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-14');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-15');
insert into testing_data (product,stock_amount,date_of_check) values ('Superhero masks',NULL,'2022-02-13');

Everyone has received data in the past that’s been perfectly legible for normal humans but useless for machines, a prime example of this is the following dataset from Superhero Inc. :

The inventory management has been a bit spotty with irregular checks on different dates, and an inventory system that can really use some TLC.

We can easily extrapolate that the stock amount hasn’t been filled out because it hasn’t changed and that the previous value that HAS been filled out still applies.

Translating this into SQL however, is your challenge for today.

We would like to transform the above column, into the following :

We secretly know that Snowflake has a way to do this a bit more easily in comparison to other systems so if you REALLY want to challenge yourself, you can try to do it without any Snowflake-specific tools.

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‘ (note joining our mailing list does not give you a Frosty Friday account)
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.

You can also sign up to our mailing list below.

by Christopher Marland on 2022-09-02 5 Comments

Week 12 – Intermediate

This week’s challenge is another Streamlit challenge, we want you to produce an interface where you can upload files into whichever table is selected.

You will find your setup code here

create schema <enter_db_name_here>.world_bank_metadata;

create or replace table <enter_db_name_here>.world_bank_metadata.country_metadata
(
    country_code varchar(3),
    region string,
    income_group string
);

create schema <enter_db_name_here>.world_bank_economic_indicators;

create or replace table <enter_db_name_here>.world_bank_economic_indicators.gdp
(
    country_name string,
    country_code varchar(3),
    year int,
    gdp_usd double
);

create table <enter_db_name_here>.world_bank_economic_indicators.gov_expenditure
(
    country_name string,
    country_code varchar(3),
    year int,
    gov_expenditure_pct_gdp double
);

create schema <enter_db_name_here>.world_bank_social_indiactors;

create or replace table <enter_db_name_here>.world_bank_social_indiactors.life_expectancy
(
    country_name string,
    country_code varchar(3),
    year int,
    life_expectancy float
);

create or replace table <enter_db_name_here>.world_bank_social_indiactors.adult_literacy_rate
(
    country_name string,
    country_code varchar(3),
    year int,
    adult_literacy_rate float
);

create or replace table <enter_db_name_here>.world_bank_social_indiactors.progression_to_secondary_school
(
    country_name string,
    country_code varchar(3),
    year int,
    progression_to_secondary_school float
);

Unfortunately, basic Python knowledge is essential for this week. But, it’s nothing a quick beginner’s course couldn’t quickly teach you!

You can find the data here, and the FF logo here.

RESULT

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‘ (note joining our mailing list does not give you a Frosty Friday account)
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.

You can also sign up to our mailing list below.

Archives for September 2022