Week 27 – Basic

It’s hard to keep up with new Snowflake features coming out left, right and center. Here’s one that’s created a lot of buzz in the community – EXCLUDE and RENAME. So simple, and yet so much time saved!

Start Up Code
create or replace table frosty_db.challenges.week27 
(
    icecream_id int,
    icecream_flavour varchar(15),
    icecream_manufacturer varchar(50),
    icecream_brand varchar(50),
    icecreambrandowner varchar(50),
    milktype varchar(15),
    region_of_origin varchar(50),
    recomendad_price number,
    wholesale_price number
);

insert into frosty_db.challenges.week27 values
    (1, 'strawberry', 'Jimmy Ice', 'Ice Co.', 'Food Brand Inc.', 'normal', 'Midwest', 7.99, 5),
    (2, 'vanilla', 'Kelly Cream Company', 'Ice Co.', 'Food Brand Inc.', 'dna-modified', 'Northeast', 3.99, 2.5),
    (3, 'chocolate', 'ChoccyCream', 'Ice Co.', 'Food Brand Inc.', 'normal', 'Midwest', 8.99, 5.5);

Take the start up code above, and write a SELECT * query that:

  1. Excludes milktype
  2. Renames icecreambrandowner to ice_cream_brand_owner

This one should only take 5 minutes!

Remember, if you want to participate:

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

10 responses to “Week 27 – Basic”

  1. ChrisHastie avatar
    ChrisHastie

    A short challenge indeed, but still a very useful one. Thanks for highlighting the new functionality, I’ve already used it several times to massively simplify scripts on larger column sets!

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

    I had no clue about this feature. Thanks for highlighting it this week.

    • vdharst avatar
      vdharst

      My first FF!

      • Solution URL – https://github.com/vdharst/Frosty_Fridays/blob/main/week_27
    • fengliplatform avatar
      fengliplatform

      Learn something new every day!

    • Jamie Laird avatar
      Jamie Laird

      Nice intro to these parameters

      • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_27/ff_week_27.sql
    • zlzlzl2 avatar
      zlzlzl2

      Nice little trick to know.

      • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF27_LZ.sql
    • Atzmonky avatar
      Atzmonky

      Indeed simple but quite handy…

      • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/FFch27
    • arjan.loogman avatar
      arjan.loogman

      Short ‘n sweet!

      • Solution URL – https://github.com/arjansnowflake/Frosty_Friday/blob/main/Week_27/week_27.sql
    • Non-deterministicNorman avatar
      Non-deterministicNorman

      short and sweet

      • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2027%20-%20basic%20/SELECT%20EXCLUDE%20RENAME

    Leave a Reply