Christopher Marland

by Christopher Marland on 2024-07-19 1 Comment

Week 102 – Intermediate

Snowpark Pandas! Finally, our love-hate relationship with Pandas becomes one completely full of love as we use familiar, well-known syntax on the distributed Snowflake system.

This week’s challenge tasks you with using Snowpark Pandas to answer 5 rapid-fire questions!

Below is your start-up SQL code:

create or replace stage frosty_stage url = 's3://frostyfridaychallenges/challenge_102/';

Then open up a notebook and place the following in the first cell:

import modin.pandas as pd
import snowflake.snowpark.modin.plugin

from snowflake.snowpark.context import get_active_session
session = get_active_session()

clothes_shop_df = pd.read_csv('@frosty_stage/clothes_shop_purchases.csv')

After that, you’re ready to answer the following 4 questions!

At what hour of the day are the majority of our sales?
Which server sold the most?
What is the total price like if we deduct 20% for tax on the first five items?
What would the biggest till number be if we merged tills 4 and 5?

Answers

by Christopher Marland on 2024-06-28 1 Comment

Week 99 – Intermediate

Document AI is one of the most exciting of the new features released over the past year. It’s definitely going to help us work through our invoices!

Download these files and upload them to an internal stage.

Once you’ve finished, we want you to extract all of the data you can see below:

Happy extracting!

by Christopher Marland on 2024-05-31 2 Comments

Week 96 – Basic

DevOps nerds galore celebrate as CREATE OR ALTER goes into public preview! What a great pre-Summit drop, we can use it in our attendee tracking table.

CREATE OR TABLE conference_attendees (
    attendee_id INT,
    first_name STRING,
    last_name STRING,
    email STRING,
    registration_date DATE,
    ticket_type STRING,
    conference_id INT
);

INSERT INTO conference_attendees VALUES
(1, 'John', 'Doe', 'john.doe@example.com', '2024-06-01', 'DSH', 101),
(2, 'Jane', 'Smith', 'jane.smith@example.com', '2024-06-02', 'Regular', 101),
(3, 'Alice', 'Johnson', 'alice.johnson@example.com', '2024-06-03', 'DSH', 102),
(4, 'Bob', 'Brown', 'bob.brown@example.com', '2024-06-04', 'Regular', 102),
(5, 'Charlie', 'Davis', 'charlie.davis@example.com', '2024-06-05', 'Partner', 103),
(6, 'Diana', 'Martinez', 'diana.martinez@example.com', '2024-06-06', 'Regular', 101),
(7, 'Evan', 'Garcia', 'evan.garcia@example.com', '2024-06-07', 'DSH', 103),
(8, 'Fiona', 'Wilson', 'fiona.wilson@example.com', '2024-06-08', 'Regular', 102),
(9, 'George', 'Moore', 'george.moore@example.com', '2024-06-09', 'Partner', 103),
(10, 'Hannah', 'Taylor', 'hannah.taylor@example.com', '2024-06-10', 'DSH', 101);

Wait no… we’ve messed that up!

conference_id ought to be conference_name, and everyone’s going to Summit 24! Use CREATE OR ALTER to rectify our mistake.

If you’ve done it right, the below should work:

INSERT INTO conference_attendees VALUES
(11, 'Irene', 'Lee', 'irene.lee@example.com', '2024-06-11', 'DSH', 'Summit 2024'),
(12, 'Jack', 'Harris', 'jack.harris@example.com', '2024-06-12', 'Regular', 'Summit 2024'),
(13, 'Karen', 'Clark', 'karen.clark@example.com', '2024-06-13', 'DSH', 'Summit 2024'),
(14, 'Larry', 'Lewis', 'larry.lewis@example.com', '2024-06-14', 'Regular', 'Summit 2024'),
(15, 'Monica', 'Walker', 'monica.walker@example.com', '2024-06-15', 'Partner', 'Summit 2024'),
(16, 'Nina', 'Hall', 'nina.hall@example.com', '2024-06-16', 'Regular', 'Summit 2024'),
(17, 'Oscar', 'Young', 'oscar.young@example.com', '2024-06-17', 'DSH', 'Summit 2024'),
(18, 'Paula', 'King', 'paula.king@example.com', '2024-06-18', 'Regular', 'Summit 2024'),
(19, 'Quinn', 'Wright', 'quinn.wright@example.com', '2024-06-19', 'Partner', 'Summit 2024'),
(20, 'Rachel', 'Lopez', 'rachel.lopez@example.com', '2024-06-20', 'DSH', 'Summit 2024');

Happy Altering!

by Christopher Marland on 2024-05-10 1 Comment

Week 93 – Advanced

External Access Integrations are in GA now (well, for a few weeks but there’s so much coming out that even we can’t keep up!) and so it’s a good time to create a challenge based around them!

Note: for this, you will need an admin to run the following code:

CREATE OR REPLACE NETWORK RULE treasury_nr
  MODE = EGRESS
  TYPE = HOST_PORT
  VALUE_LIST = ('api.fiscaldata.treasury.gov');


CREATE OR REPLACE EXTERNAL ACCESS INTEGRATION treasury_eai
  ALLOWED_NETWORK_RULES = (treasury_nr)
  ENABLED = true;

GRANT USAGE ON INTEGRATION treasury_eai TO ROLE <your_role>;

Once they’ve done that, now it’s for you to create the table:

CREATE OR REPLACE TABLE WEEK_93 (
    AVG_INTEREST_RATE_AMT FLOAT,
    RECORD_DATE DATE,
    SECURITY_DESC STRING,
    SECURITY_TYPE_DESC STRING,
    SRC_LINE_NBR STRING,
    API_CALL_START_DATE DATE,
    API_CALL_END_DATE DATE
);

You then need to create a function with the requests library that looks something like:

CREATE OR REPLACE FUNCTION get_treasury_data(start_date DATE, end_date DATE)
RETURNS VARIANT
LANGUAGE PYTHON
RUNTIME_VERSION = 3.8
HANDLER = 'get_data'
EXTERNAL_ACCESS_INTEGRATIONS = (treasury_eai)
PACKAGES = ('snowflake-snowpark-python','requests')
AS
$$

$$;

You want to query the following API:

https://api.fiscaldata.treasury.gov/services/api/fiscal_service//v2/accounting/od/avg_interest_rates?fields=record_date,security_type_desc,security_desc,avg_interest_rate_amt,src_line_nbr&filter=record_date:gte:<START_DATE_HERE>,record_date:lt:<END_DATE_HERE>

Your data must start at 2020-01-01 and finish at 2024-04-30. Create a stored procedure (in the language of your choosing) that loops through the result until it can get no more data between those dates and INSERTs the data into your table.

If you get this correct, your table should have 880 rows, and they ought to look like:

by Christopher Marland on 2024-04-19 3 Comments

Week 90 – Intermediate

Can you predict the future? No? Well, Snowflake kinda can! How? Well it’s those Cortex functions that everyone keeps going on about.

This week we’ll be using one of their ML functions.

Your job is to get the training data:

CREATE STAGE IF NOT EXISTS frosty_aws_stage
  URL = 's3://frostyfridaychallenges/';

CREATE OR REPLACE TABLE WEEK_90 AS 
SELECT 
    $1::TIMESTAMP_NTZ AS SALE_DATE, 
    $2::INT AS PRODUCT_ID,
    $3::INT AS QUANTITY_SOLD,
    $4::INT AS UNIT_PRICE,
    $5/100::FLOAT AS TAX_PCT,
    $6/100::FLOAT AS DCT_PCT
FROM @FROSTY_AWS_STAGE/challenge_90
WHERE $1 != 'Date';

And see what Snowflake has to say forecasting-wise about the below:

CREATE OR REPLACE TABLE WEEK_90_F LIKE WEEK_90;
ALTER TABLE WEEK_90_F DROP COLUMN QUANTITY_SOLD;

INSERT INTO WEEK_90_F VALUES 
(TO_TIMESTAMP_NTZ('2023-10-29'), 1000, 450, 0.1, 0.02),
(TO_TIMESTAMP_NTZ('2023-10-29'), 1001, 150, 0.15, 0.02),
(TO_TIMESTAMP_NTZ('2023-10-29'), 1002, 100, 0.13, 0.18),
(TO_TIMESTAMP_NTZ('2023-10-29'), 1003, 170, 0.11, 0.03),
(TO_TIMESTAMP_NTZ('2023-10-29'), 1004, 300, 0.04, 0.03);

Your output should look like the below:

Happy forecasting!

by Christopher Marland on 2024-03-29 9 Comments

Week 87 – Basic

Coortex, Cortex, Cortex… all you hear about is Cortex, and we want to give you plenty of opportunity to use all the new Cortex functions here at FROSTY_FRIDAY()

And of course this is a basic challenge because… well, the point of Cortex is to make data science very, very easy!

This week, we’ll be using the translate function. Below is your start up code:

CREATE OR REPLACE TABLE WEEK_87 AS
SELECT 
  'Happy Easter' AS greeting,
  ARRAY_CONSTRUCT('DE', 'FR', 'IT', 'ES', 'PL', 'RO', 'JA', 'KO', 'PT') AS language_codes
;

Your end result should look like the below:

Good luck!

by Christopher Marland on 2024-03-08 4 Comments

Week 84 – Basic

You might not have noticed that Snowflake released a sneaky new preview feature that lets you do file movement operation between Snowflake stages.

How?

You better find out.

Here’s your start up code:

CREATE STAGE frosty_aws_stage
  URL = 's3://frostyfridaychallenges/';

LIST @FROSTY_AWS_STAGE/challenge_84/;

CREATE STAGE WEEK84;

You need to run a SQL command that will produce:

Good luck figuring it out!

by Christopher Marland on 2024-02-16 1 Comment

Week 81 – Intermediate

Did you know that one task can have more than one parent? No?

Run the following code:

CREATE STAGE frosty_aws_stage
  URL = 's3://frostyfridaychallenges/';

EXECUTE IMMEDIATE FROM @frosty_aws_stage/challenge_81/starter_code.sql;

Now you want to create 5 tasks:

Task 1 will run the following:

Task 1

INSERT INTO w81_raw_product (data)
SELECT parse_json(column1)
FROM
VALUES
('{"product_id": 21, "product_name": "Product U", "category": "Electronics", "price": 120.99, "created_at": "2024-02-16"}'),
('{"product_id": 22, "product_name": "Product V", "category": "Books", "price": 35.00, "created_at": "2024-02-16"}');
INSERT INTO w81_raw_customer (data)
SELECT parse_json(column1)
FROM
VALUES
('{"customer_id": 6, "customer_name": "Frank", "email": "frank@example.com", "created_at": "2024-02-16"}'),
('{"customer_id": 7, "customer_name": "Grace", "email": "grace@example.com", "created_at": "2024-02-16"}');
INSERT INTO w81_raw_sales (data)
SELECT parse_json(column1)
FROM
VALUES
('{"sale_id": 11, "product_id": 21, "customer_id": 6, "quantity": 1, "sale_date": "2024-02-17"}'), -- New product, new customer
('{"sale_id": 12, "product_id": 22, "customer_id": 1, "quantity": 1, "sale_date": "2024-02-17"}'), -- New product, existing customer
('{"sale_id": 13, "product_id": 2, "customer_id": 7, "quantity": 2, "sale_date": "2024-02-17"}'), -- Existing product, new customer
('{"sale_id": 14, "product_id": 3, "customer_id": 6, "quantity": 1, "sale_date": "2024-02-17"}'), -- Existing product, new customer
('{"sale_id": 15, "product_id": 21, "customer_id": 5, "quantity": 1, "sale_date": "2024-02-17"}'); -- New product, existing customer;

Tasks 2-4 will parse the JSON from the raw tables

Task 5 will run the following AFTER tasks 2-4 have completed:

Task 5

CREATE OR replace VIEW aggregated_sales
AS
  SELECT c.customer_name,
         p.product_name,
         SUM(s.quantity)           AS total_quantity,
         SUM(s.quantity * p.price) AS total_sales
  FROM   sales s
         join product p
           ON s.product_id = p.product_id
         join customer c
           ON s.customer_id = c.customer_id
  GROUP  BY c.customer_name,
            p.product_name

In the end, you should run:

End run

SELECT t.name,
       t.predecessors
FROM   TABLE(information_schema.Task_dependents(task_name => 'INSERT_INTO_RAW', recursive => TRUE)) t;

And get:

Happy DAGing!

by Christopher Marland on 2024-01-26 3 Comments

Week 78 – Basic

Session Variables!

Did you even know that you can use variables in your SQL queries outwith stored procedures? No? Well, I’m here to tell you you can.

Set Up Code

CREATE TABLE w78 AS
SELECT
  SEQ4() AS sales_id,
  CASE
    WHEN MOD(SEQ4(), 4) = 0 THEN 'Product A'
    WHEN MOD(SEQ4(), 4) = 1 THEN 'Product B'
    WHEN MOD(SEQ4(), 4) = 2 THEN 'Product C'
    ELSE 'Product D'
  END AS product_name,
  UNIFORM(1, 10, RANDOM())::INTEGER AS quantity_sold,
  DATEADD('day', -UNIFORM(1, 365, RANDOM())::INTEGER, CURRENT_DATE()) AS sales_date,
  UNIFORM(20, 100, RANDOM())::FLOAT * UNIFORM(1, 10, RANDOM())::INTEGER AS sales_amount
FROM TABLE(GENERATOR(ROWCOUNT => 1000));

Run the above code, and then create a variable called “sales_avg”.

Run the below script:

SELECT *
FROM w78
WHERE sales_amount between $sales_avg - 50 and $sales_avg +50;

Your result should look like the below (numbers will vary)

by Christopher Marland on 2023-12-15 2 Comments

Week 75 – Hard

One of the cool things about native apps, is the ability to request permissions and be granted them without actually having real access to the consumer account.

Instead, the application gets the permissions.

But how do we request permissions? Well, let’s find out. Your challenge is to request:

SELECT access to one table
CREATE DATABASE on account

Your native app should then verify it has all the permissions it needs. The flow should look like:

Here is a zip with the skeleton code. You will need to change every file in the zip.

Good luck!

2 responses to “Week 75 – Hard”

mferle

2023-12-22
This one was tough! Still didn’t get it quite right, as I don’t intuitively understand Streamlit behavior. But got it approximately working with some help from the authors 🙂
- Solution URL – https://github.com/mferle/FrostyFriday/tree/main/Week%2075%20-%20Hard
Log in to Reply
ChrisHastie

2024-03-26
I missed this challenge initially and shamefully then forgot about it. I’ve just finished building a fleshed out app for a customer and going back to complete this challenge was a rewarding way to show myself that I have learned from the experience.

Thanks for continuing to provide useful and interesting challenges
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
Log in to Reply

Christopher Marland

2 responses to “Week 75 – Hard”

Leave a Reply Cancel reply