by 
Following week 57, we’re continuing with our SELECT options with REPLACE!
Given our start-up code below, we’d want you to do the following :
– subtract 1 from age (because the intern forgot that babies start at 0 , not 1)
– add 10% to everyone’s weight because the scale seemed to be off
– replace everyone’s gender to ‘unknown ‘ if it was marked ‘other’.
Start-up code
CREATE OR REPLACE TABLE measurements (
height INT,
weight INT,
age INT,
gender VARCHAR(),
id INT
);
insert into measurements (height, weight, age, gender, id) values (41, 178, 74, 'other', 1);
insert into measurements (height, weight, age, gender, id) values (188, 145, 87, 'other', 2);
insert into measurements (height, weight, age, gender, id) values (215, 725, 30, 'male', 3);
insert into measurements (height, weight, age, gender, id) values (159, 48, 116, 'female', 4);
insert into measurements (height, weight, age, gender, id) values (243, 204, 6, 'other', 5);
insert into measurements (height, weight, age, gender, id) values (232, 306, 30, 'male', 6);
insert into measurements (height, weight, age, gender, id) values (261, 602, 62, 'other', 7);
insert into measurements (height, weight, age, gender, id) values (143, 829, 113, 'female', 8);
insert into measurements (height, weight, age, gender, id) values (62, 190, 86, 'male', 9);
insert into measurements (height, weight, age, gender, id) values (249, 15, 73, 'male', 10);The challenge is to do this with 1 query, with the end results looking like this :
(there might be a small trap hidden in our challenge this week so don’t get discouraged if you don’t get it on your first attempt)
9 responses to “Week 58 – Basic”
-
Another quick challenge to highlight new functionality, thank you
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
My first challenge. Thanks FF
- Solution URL – https://github.com/anabelcarol/Frosty-Friday
-
Really cool. Thanks for the challenge!
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK58.sql
-
Thank you for pointing out new features. This is my default place for what’s new in Snowflake 🙂
- Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w59.py
-
Didn’t understand the question first, until seen others’ solutions. Idea is wrap everything within a replace.
- Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF58_LZ.sql
-
different ways to skin a cat. Applied REPLACE to just the gender column.
- Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2058%20-%20basic%20/REPLACE
-
Nice little new feature in Snowflake
- Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_58.sql
-
Nice to get back into it. Here is my solution:
- Solution URL – https://github.com/EmoktayrE/Frosty_Friday_Challenges/blob/main/ffchallenge58.sql
-
In my solution I decided to compare the CASE WHEN with the REPLACE features.
- Solution URL – https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2058%20%E2%80%93%20Basic
Leave a Reply
You must be logged in to post a comment.