by 
We’re going to give you something old and something new for this challenge (sadly not borrowed or blue).
We’re taking a look at the new Array functions released in the last release and also looking into Recursive CTEs!
The challenge is a 2-parter ;
Part #1
Create a table that looks like this using purely snowflake-based functions:
| Cart_number | Contents |
| 1 | [5,10,15,20] |
| 2 | [8,9,10,11,12,13,14] |
Part #2:
Use the following table to ‘unpack’ your cart :
| Cart_number | Content_to_remove | Order_to_remove_in |
| 1 | 10 | 1 |
| 1 | 15 | 2 |
| 1 | 5 | 3 |
| 1 | 20 | 4 |
| 2 | 8 | 1 |
| 2 | 14 | 2 |
| 2 | 11 | 3 |
| 2 | 12 | 4 |
| 2 | 9 | 5 |
| 2 | 10 | 6 |
| 2 | 13 | 7 |
your answer should create a table with the following columns :
– Cart_number
– Current_contents_of_cart
– content_last_removed
good luck unpacking!
4 responses to “Week 46 – Intermediate”
-
I wasn’t aware of some of these new array functions. Thanks for another fun challenge
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
That was fun, thanks for the challenge.
- Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_46.sql
-
ARRAY_CONSTRUCT & ARRAY_REMOVE
- Solution URL – https://github.com/canonicalized/FrostyFriday/blob/main/WEEK46.sql
-
Nice way to learn recursive CTEs in Snowflake
- Solution URL – https://github.com/ChrisBo94/FrostyFriday/blob/main/Week_46.sql
Leave a Reply
You must be logged in to post a comment.