Week 16 – Intermediate

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

Below we have the set up script:

Set Up Code

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%';

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:

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

11 responses to “Week 16 – Intermediate”

ChrisHastie

2022-09-30
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
Log in to Reply
dsmdavid

2022-09-30
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
Log in to Reply
mat

2022-09-30
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
Log in to Reply
scallybrian

2022-10-03
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
Log in to Reply
Pekka Kanerva

2022-10-18
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
Log in to Reply
Atzmonky

2022-10-26
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
Log in to Reply
Cesare Simonini

2023-01-26
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
Log in to Reply
AnastasiaKomissaroff

2023-02-03
Hello!

My solution for this week 🙂
- Solution URL – https://github.com/anakomissaroff/Frosty_Friday/blob/main/week16.sql
Log in to Reply
zlzlzl2

2023-03-15
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
Log in to Reply
Daryl Kit

2024-02-21
🙂
- Solution URL – https://github.com/darylkit/Frosty_Friday/blob/main/Week%2016%20-%20JSON/json.sql
Log in to Reply
daisuke

2024-07-21
YeY
- Solution URL – https://github.com/Mya-mori/Frosty_Friday/blob/main/week16/notebook_sql.ipynb
Log in to Reply

Comments

ChrisHastie says

2022-09-30 at 09:09
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
Log in to Reply
dsmdavid says

2022-09-30 at 15:32
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
Log in to Reply
mat says

2022-09-30 at 15:34
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
Log in to Reply
scallybrian says

2022-10-03 at 11:27
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
Log in to Reply
Pekka Kanerva says

2022-10-18 at 14:04
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
Log in to Reply
Atzmonky says

2022-10-26 at 14:25
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
Log in to Reply
Cesare Simonini says

2023-01-26 at 10:46
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
Log in to Reply
AnastasiaKomissaroff says

2023-02-03 at 15:22
Hello!

My solution for this week 🙂
- Solution URL - https://github.com/anakomissaroff/Frosty_Friday/blob/main/week16.sql
Log in to Reply
zlzlzl2 says

2023-03-15 at 18:15
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
Log in to Reply
Daryl Kit says

2024-02-21 at 06:25
🙂
- Solution URL - https://github.com/darylkit/Frosty_Friday/blob/main/Week%2016%20-%20JSON/json.sql
Log in to Reply
daisuke says

2024-07-21 at 02:46
YeY
- Solution URL - https://github.com/Mya-mori/Frosty_Friday/blob/main/week16/notebook_sql.ipynb
Log in to Reply

11 responses to “Week 16 – Intermediate”

Leave a Reply Cancel reply

Reader Interactions

Comments

Leave a Reply Cancel reply