Snowflake Trial

Week 58 – Basic

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’.

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)

7 responses to “Week 58 – Basic”

  1. ChrisHastie avatar
    ChrisHastie

    Another quick challenge to highlight new functionality, thank you

    • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
  2. av avatar
    av

    My first challenge. Thanks FF

    • Solution URL – https://github.com/anabelcarol/Frosty-Friday
  3. canonicalized avatar
    canonicalized

    Really cool. Thanks for the challenge!

    • Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK58.sql
  4. mat avatar
    mat

    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
  5. zlzlzl2 avatar
    zlzlzl2

    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
  6. Non-deterministicNorman avatar
    Non-deterministicNorman

    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
  7. Chris B avatar
    Chris B

    Nice little new feature in Snowflake

    • Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_58.sql

Leave a Reply