Frosty Friday

Snowflake Weekly Challenges

Archives for March 2024

by on 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 on 2 Comments

Week 86 – Difficult

Welcome to the Frosty_Friday Aggregation Policy challenge! You are tasked with creating SQL queries that comply with Snowflake’s data aggregation policy. Your queries should aggregate data from a given table or view, ensuring that each group meets the minimum size requirement specified in the aggregation policy.

About aggregation policy:
Snowflake offers a feature to share datasets while maintaining control over data usage through aggregation policies. These policies allow data owners to specify minimum group sizes for aggregation, enhancing data privacy and security.

Requirements:

  1. Utilize SQL queries to aggregate data from the specified table or view.
  2. Adhere to the allowed aggregation functions permitted by the aggregation policy.
  3. Ensure that each group formed by the query contains at least the specified minimum number of records as per the aggregation policy.

Instructions:

  1. Use the code below to create a sample table named Sales_Records
  2. Define an aggregation policy with a minimum group size (X).
  3. Create SQL queries that aggregate data from the chosen dataset, adhering to the aggregation policy requirements.
  4. Test your queries to ensure compliance with the policy.

To create sample table Sales_Records

May the aggregation be with you!

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‘ (note joining our mailing list does not give you a Frosty Friday account)
  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.

by on 3 Comments

Week 85 – Intermediate

Time to try out this ASOF join that they’ve introduced to Snowflake!

In the heart of a bustling financial district lies a brokerage firm’s trading floor, where traders make split-second decisions amid a frenzy of activity. Trades and quotes, recorded in separate tables, hold vital information about transactions and market prices.

To ensure traders have the latest market data, a critical task emerges: pairing each trade with the most recent quote available at or before its time. Analysts devise a sophisticated SQL query, leveraging temporal joins to seamlessly integrate trade and quote data based on timestamps.

Given two tables, trade and quotes, containing transactional data in a financial market, where the trade table records trades and the quotes table records quotes for a specific financial instrument, construct an ASOF join SQL statement to pair each trade record with the most recent quote record available at or before the time of the trade.

Start up Code

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‘ (note joining our mailing list does not give you a Frosty Friday account)
  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.

by on 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 on 3 Comments

Week 83 – Basic

You are tasked with optimizing a query in Snowflake that extracts information from a table called sales_data. The sales_data table contains information about sales transactions, including columns like product_id, quantity_sold, price, and transaction_date.

Your goal? Retrieving the top 10 products with the highest total revenue, where the total revenue is calculated as the sum of the product of quantity_sold and price for each transaction.

Hint: In a SELECT statement, the QUALIFY clause filters the results of window functions.

To create the table

-- Create sales_data table
CREATE TABLE sales_data (
  product_id INT,
  quantity_sold INT,
  price DECIMAL(10,2),
  transaction_date DATE
);

-- Insert sample values
INSERT INTO sales_data (product_id, quantity_sold, price, transaction_date)
VALUES
  (1, 10, 15.99, '2024-02-01'),
  (1, 8, 15.99, '2024-02-05'),
  (2, 15, 22.50, '2024-02-02'),
  (2, 20, 22.50, '2024-02-07'),
  (3, 12, 10.75, '2024-02-03'),
  (3, 18, 10.75, '2024-02-08'),
  (4, 5, 30.25, '2024-02-04'),
  (4, 10, 30.25, '2024-02-09'),
  (5, 25, 18.50, '2024-02-06'),
  (5, 30, 18.50, '2024-02-10');

Be sure to share your coded answers in the comments!

Thank you so much for the great advice Can Höbek! Your contribution was very helpful, how would I do without you.

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‘ (note joining our mailing list does not give you a Frosty Friday account)
  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.