July 2023

by Mike Droog on 2023-07-28 3 Comments

Week 56 – Intermediate

Welcome to the Emoji Translator and Notification challenge! Your task is to develop a program that will tackle the usage of emojis. Our external partner supplies you with a file formatted as a CSV containing various emojis, and your mission is to translate these emojis into their corresponding typed format.
Even though they ensured us that these 3 emojis are the only ones to be used, we know from experience that that could change in a heartbeat.

Phase 1: Emoji Translation Your program should read the provided CSV file, extract the emojis, and convert each emoji to its respective typed format. For example, 🚀 should be translated to “:rocket:” and 😀 should become “:grinning:”.

Phase 2: Emoji Notification We always want to stay ahead in the emoji game! Implement a notification system that will alert you whenever a new emoji appears in the CSV file. Your program should automatically detect any recently added emojis and send a message notifying you of the new discovery.

Frequency of Checks To keep the system up-to-date, your program should perform emoji checks once every week. This means you’ll need to schedule a recurring system that examines the CSV file for any additions.

Guidelines:

The program should be written in a language of your choice.

Get ready to embark on this emoji-filled adventure and have fun coding! Good luck! 🚀😀👍

Required Files:
The location for the new files is the s3://frostyfridaychallenges/challenge_56/ bucket.
This bucket contains 2 files: survey_resulsts.csv with the agreed upon emojis and 1, survey_results_2.csv with a ‘new’ emoji

3 responses to “Week 56 – Intermediate”

canonicalized

2023-07-28
Fun challenge this week. Pay attention to whitespaces in the emojis column
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK56.sql
Log in to Reply
ChrisHastie

2023-07-31
This was a fun one. I tried to be clever and ingest a full package to do it all, but hit some issues importing nested modules. Fortunately, you can get around that at some point with a private preview feature in the following article, but I didn’t want to use a private preview feature here so I just stole the mapping file from the emoji module I found instead.

https://medium.com/snowflake/using-other-python-packages-in-snowpark-a6fd75e4b23a
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply
gaku_t

2025-06-26
Thank you so much for continuously creating such enjoyable challenges.
Frosty Friday is like tech therapy for me—it lets me explore features I don’t usually touch at work, and it’s a great way to refresh my mind!

This is a solution I came up with during the Frosty Friday Live Challenge held in Japan.
This time, I also tried solving it using the new Workspaces feature announced at the Summit.

The data ingestion part was something I tackled in a previous challenge, and it’s now been refined quite a bit—with improvements like parse_header, infer_schema, and copy into with include_metadata.

以下、日本語
楽しいお題を継続して作成してくださって本当にありがとうございます。
FrostyFridayは業務ではやらない機能をさわれるテックセラピーです！リフレッシュできています！

日本でFrosty Friday Live Challangeをやっていてその中での解法です。
今回は、Summitで発表されたWorkspaceを使っても解いてみました。

データの取込の部分は、以前のチャレンジでやったところで、かなりリファインされたかたちになっています（parse_headerやinfer_schemaや copy into include_metadata）
- Solution URL – https://github.com/gakut12/Frosty-Friday/tree/main/week056_intermediate_udf_and_alerts
Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

by Christopher Marland on 2023-07-21 6 Comments

Week 55 – Basic

Duplicates! The scourge of every data engineer. Our sales data today has a couple of them. Your job is to read the data from an external stage, and clean it up. Expunge the duplicates using GROUP BY ALL that shiney new SQL command we’ve all been given.

Start Up Code

create or replace stage week55_stage
    url='s3://frostyfridaychallenges/challenge_55/';

At the end your data should look like:

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

6 responses to “Week 55 – Basic”

ChrisHastie

2023-07-21
Another nice challenge to demonstrate new functionality, thanks!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply
canonicalized

2023-07-21
Lovely feature!
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK55.sql
Log in to Reply
dsmdavid

2023-08-08
Thanks for the challenge! This will be useful with SELECT ILIKE
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_55.sql
Log in to Reply
Non-deterministicNorman

2023-11-05
picked up on the qualify clause as an added bonus
- Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2055%20-%20basic%20/GROUPBYALL
Log in to Reply
Chris B

2023-11-07
Another nice new feature to learn about
- Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_55.sql
Log in to Reply
gergana98

2024-05-14
I was not sure how to infer the schema so I tried to guess it just in case it was different from the one in the result picture here. I didn´t see any duplicates either, so when using the group by all I didn´t notice any changes. Any ideas?
- Solution URL – https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2055%20%E2%80%93%20Basic
Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

by Mike Droog on 2023-07-14 1 Comment

Week 54 – Intermediate

Not every new feature had a big announcement at Summit 2023, but that doesn’t mean we can’t spotlight it!

This week we’ve got a puzzle for you to crack and without spoiling everything, let’s see if you can figure out the puzzle from the starting code :

Startup Code

-- database and schema creation
CREATE DATABASE F_F_WEEK_54;
CREATE SCHEMA WEEK_54;

--table creation
CREATE TABLE table_a (
id INT,
name VARCHAR,
age INT
);

CREATE TABLE table_b (
id INT,
name VARCHAR,
age INT
);

--data creation
INSERT INTO table_a (id, name, age)
VALUES
(1, 'John', 25),
(2, 'Mary', 30),
(3, 'David', 28),
(4, 'Sarah', 35),
(5, 'Michael', 32),
(6, 'Emily', 27),
(7, 'Daniel', 29),
(8, 'Olivia', 31),
(9, 'Matthew', 26),
(10, 'Sophia', 33),
(11, 'Jacob', 24),
(12, 'Emma', 29),
(13, 'Joshua', 32),
(14, 'Ava', 30),
(15, 'Andrew', 28),
(16, 'Isabella', 34),
(17, 'James', 27),
(18, 'Mia', 31),
(19, 'Logan', 25),
(20, 'Charlotte', 29);

--role creation
CREATE ROLE week_54_role;
GRANT ROLE week_54_role to user <user_name> ;
GRANT USAGE ON database F_F_WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON schema WEEK_54 TO ROLE week_54_role;
GRANT SELECT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT INSERT ON ALL TABLES IN SCHEMA F_F_WEEK_54.WEEK_54 TO ROLE week_54_role;
GRANT USAGE ON WAREHOUSE TO ROLE week_54_role;

As you can see, we create 2 simple tables and a new role that can select and insert from these tables. We now wish to use that role to create and execute a stored procedure to move data from table A to table B. HOWEVER, the role that we’ve created doesn’t have the CREATE PROCEDURE privilege!

The challenge for today is to create and execute the following procedure without granting the CREATE PROCEDURE privilege:

Procedure

CREATE OR REPLACE copy_to_table(fromTable STRING, toTable STRING, count INT) RETURNS STRING LANGUAGE PYTHON RUNTIME_VERSION = '3.8' PACKAGES = ('snowflake-snowpark-python') HANDLER = 'copyBetweenTables' AS $$ def copyBetweenTables(snowpark_session, fromTable, toTable, count): snowpark_session.table(fromTable).limit(count).write.mode("append").save_as_table(toTable) return "Success" $$ ;

You solution should keep the procedure intact, and this piece of code needs to be part of your solution:


CALL copy_to_table('table_a', 'table_b', 5);

One response to “Week 54 – Intermediate”

ChrisHastie

2023-07-14
Another fun challenge, thank you. To keep up the mystery, I strongly hope nobody looks at each other’s solutions until they’ve figured it out themselves!
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

by Christopher Marland on 2023-07-07 3 Comments

Week 53 – Intermediate

One of the quieter releases into public preview is that we can now infer schemas for CSVs. To test this follow the following instructions:

Create an internal stage

create stage frosty_stage;
Create a csv file format
create or replace file format frosty_csv field_optionally_enclosed_by = '"' skip_header = 1;
PUT the following csv file into that internal stage. Download Here

Now you need to infer the schema and produce the following:

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

3 responses to “Week 53 – Intermediate”

ChrisHastie

2023-07-07
Another week, more fun. Cheers
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply
canonicalized

2023-07-08
I actually went on to create the table with the inferred schema and loaded the data by parsing the csv header
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK53.sql
Log in to Reply
dsmdavid

2023-08-01
thanks for the challenge — had to `put` the file outside my solution, but good to explore these new functionalities (also useful to combina with other metadata functions!)
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_53.sql
Log in to Reply

Leave a Reply Cancel reply

You must be logged in to post a comment.

Archives for July 2023

3 responses to “Week 56 – Intermediate”

Leave a Reply Cancel reply

6 responses to “Week 55 – Basic”

Leave a Reply Cancel reply

One response to “Week 54 – Intermediate”

Leave a Reply Cancel reply

3 responses to “Week 53 – Intermediate”

Leave a Reply Cancel reply