Snowflake Trial

Week 16 – Intermediate

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

Below we have the set up script:

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%';
Click on the image to expand.

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:

  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

9 responses to “Week 16 – Intermediate”

  1. ChrisHastie avatar
    ChrisHastie

    My solution appears to have all 3000 distinct words, but 32,295 records instead of 28,233. However, I also notice that the screenshot is missing the following two definitions for “lab”, which appear for me on rows 19 and 20 after the other two definitions for “lab”:

    – “A Labrador retriever.”
    – “A telltale; a blabber.”

    I suspect the data has just changed since the challenge was constructed. If that’s not the case, please let me know what additional objective my solution needs to meet to have this row count.

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

    My approach…
    – Create a base table to insert the values.
    – Parse with multiple lateral flattens.
    – … getting different values (ok total distinct count –3000–, but the number of rows is not anywhere near…+32k instead of 28k…)

    I think I’m not getting the flatten quite right — are there objects in the columns as well?
    (also +1 to @ChrisHastie comment above about `lab` — but I don’t get his numbers either :sweat_smile: )

    • Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_16_02.sql
  3. mat avatar
    mat

    In the second part I received an error when I tried to define search optimization on a variant path.

    • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w16.sql
  4. scallybrian avatar
    scallybrian

    I feel like every time I go to parse json in Snowflake I’m starting from scratch again.. but it does seem to take less time so maybe it is sticking!

    • Solution URL – https://github.com/scallybrian/bs-frosty-sf/blob/main/frostyfridays/models/challenge_016/challenge_016.sql
  5. Pekka Kanerva avatar
    Pekka Kanerva

    Learned something new when the rowcounts didn’t match after the first try, reading the docs helped on resolving that problem.

    • Solution URL – https://github.com/pekka-kanerva/frosty-friday/blob/main/week16/ff-week16-solution.sql
  6. Atzmonky avatar
    Atzmonky

    I can totally relate to @scallybrian comment regarding parsing Json files.. 🙂
    but it does get better!

    • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch16_json.sql
  7. Cesare Simonini avatar
    Cesare Simonini

    Here’s my solution 🙂

    https://github.com/CSimonini/Frosty_Friday/blob/main/W16_Intermediate.sql

    • Solution URL – https://github.com/CSimonini/Frosty_Friday/blob/main/W16_Intermediate.sql
  8. AnastasiaKomissaroff avatar
    AnastasiaKomissaroff

    Hello!

    My solution for this week 🙂

    • Solution URL – https://github.com/anakomissaroff/Frosty_Friday/blob/main/week16.sql
  9. zlzlzl2 avatar
    zlzlzl2

    Number doesn’t match exactly (close enough when rounded), so I guess some may have changed.

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

Leave a Reply