SELECT with Snowflake functions - Mini Project: Build & Apply

SELECT with Snowflake functions

📖 Scenario: You are working with a Snowflake database that stores sales data. You want to practice using SELECT statements combined with Snowflake's built-in functions to analyze this data.

🎯 Goal: Build a series of SELECT queries that use Snowflake functions to extract and transform data from a sales table.

📋 What You'll Learn

Create a table called sales with columns id, amount, and sale_date

Insert sample data into the sales table

Write a SELECT query that uses the TO_VARCHAR function to convert sale_date to a string

Write a SELECT query that uses the ROUND function to round amount to 2 decimal places

Write a SELECT query that uses the DATE_PART function to extract the year from sale_date

💡 Why This Matters

🌍 Real World

Data analysts often need to transform and extract information from raw data stored in cloud databases like Snowflake. Using built-in functions in SELECT queries helps prepare data for reports and dashboards.

💼 Career

Knowing how to write SELECT queries with Snowflake functions is essential for roles like data analyst, data engineer, and cloud database administrator.

1

Create the sales table and insert data

Create a table called sales with columns id as INTEGER, amount as FLOAT, and sale_date as DATE. Then insert these exact rows: (1, 123.456, '2023-01-15'), (2, 789.123, '2023-02-20'), (3, 456.789, '2023-03-25').

Snowflake

-- Create the sales table and insert data
-- Your code here

Hint

Use CREATE OR REPLACE TABLE to create the table and INSERT INTO to add rows.

2

Select sale_date as string using TO_VARCHAR

Write a SELECT query that selects id and converts sale_date to a string using the TO_VARCHAR function. Name the converted column sale_date_str.

Snowflake

CREATE OR REPLACE TABLE sales (id INTEGER, amount FLOAT, sale_date DATE);
INSERT INTO sales (id, amount, sale_date) VALUES
(1, 123.456, '2023-01-15'),
(2, 789.123, '2023-02-20'),
(3, 456.789, '2023-03-25');

-- Write SELECT query with TO_VARCHAR for sale_date
-- Your code here

Hint

Use TO_VARCHAR(column_name) to convert a date to string.

3

Round amount to 2 decimals using ROUND

Write a SELECT query that selects id and rounds amount to 2 decimal places using the ROUND function. Name the rounded column amount_rounded.

Snowflake

CREATE OR REPLACE TABLE sales (id INTEGER, amount FLOAT, sale_date DATE);
INSERT INTO sales (id, amount, sale_date) VALUES
(1, 123.456, '2023-01-15'),
(2, 789.123, '2023-02-20'),
(3, 456.789, '2023-03-25');

SELECT id, TO_VARCHAR(sale_date) AS sale_date_str FROM sales;

-- Write SELECT query with ROUND for amount
-- Your code here

Hint

Use ROUND(column, decimals) to round numbers.

4

Extract year from sale_date using DATE_PART

Write a SELECT query that selects id and extracts the year from sale_date using the DATE_PART function. Name the extracted column sale_year.

Snowflake

CREATE OR REPLACE TABLE sales (id INTEGER, amount FLOAT, sale_date DATE);
INSERT INTO sales (id, amount, sale_date) VALUES
(1, 123.456, '2023-01-15'),
(2, 789.123, '2023-02-20'),
(3, 456.789, '2023-03-25');

SELECT id, TO_VARCHAR(sale_date) AS sale_date_str FROM sales;

SELECT id, ROUND(amount, 2) AS amount_rounded FROM sales;

-- Write SELECT query with DATE_PART to get year from sale_date
-- Your code here

Hint

Use DATE_PART('year', column) to get the year from a date.

Practice

1. What does the COUNT(*) function do in a Snowflake SELECT query?

easy

A. Calculates the sum of all numeric values in a column

B. Counts only rows with non-null values in a specific column

C. Counts all rows in the selected table or result set

D. Formats a date value into a string

Solution

1. Step 1: Understand COUNT(*) function

   The COUNT(*) counts every row regardless of nulls or values.

2. Step 2: Compare with other options

   Options B, C, and D describe different functions like COUNT(column), SUM(), and TO_CHAR().

3. Final Answer:

   Counts all rows in the selected table or result set -> Option C

4. Quick Check:

   COUNT(*) counts all rows [OK]

Hint: COUNT(*) counts every row, no matter what [OK]

Common Mistakes:

• Confusing COUNT(*) with COUNT(column)
• Thinking COUNT(*) ignores nulls
• Mixing COUNT with SUM or TO_CHAR

2. Which of the following is the correct syntax to convert a date column order_date to a string in format 'YYYY-MM-DD' using Snowflake?

easy

A. SELECT FORMAT_DATE(order_date, 'YYYY-MM-DD') FROM orders;

B. SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders;

C. SELECT TO_STRING(order_date, 'YYYY-MM-DD') FROM orders;

D. SELECT CAST(order_date AS STRING, 'YYYY-MM-DD') FROM orders;

Solution

1. Step 1: Identify the correct function for date to string

   Snowflake uses TO_CHAR() to format dates as strings.

2. Step 2: Check syntax correctness

   SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders; uses TO_CHAR(order_date, 'YYYY-MM-DD'), which is correct syntax. Others use invalid or non-existent functions.

3. Final Answer:

   SELECT TO_CHAR(order_date, 'YYYY-MM-DD') FROM orders; -> Option B

4. Quick Check:

   TO_CHAR formats dates to strings [OK]

Hint: Use TO_CHAR to format dates as strings [OK]

Common Mistakes:

• Using TO_STRING instead of TO_CHAR
• Trying FORMAT_DATE which doesn't exist in Snowflake
• Incorrect CAST syntax for formatting

3. Given the table sales with column amount, what is the result of this query?

SELECT SUM(amount) FROM sales WHERE amount > 100;

medium

A. Sum of amounts greater than 100

B. Average of amounts greater than 100

C. Count of rows where amount is greater than 100

D. Sum of all amounts including those less or equal to 100

Solution

1. Step 1: Understand the WHERE clause effect

   The WHERE clause filters rows to only those with amount > 100.

2. Step 2: Understand SUM function

   SUM adds all values of amount from the filtered rows.

3. Final Answer:

   Sum of amounts greater than 100 -> Option A

4. Quick Check:

   SUM with WHERE filters sums filtered rows [OK]

Hint: WHERE filters rows before SUM calculation [OK]

Common Mistakes:

• Including amounts less or equal to 100
• Confusing SUM with COUNT or AVG
• Ignoring WHERE clause effect

4. What is wrong with this Snowflake query?

SELECT TO_CHAR(order_date, YYYY-MM-DD) FROM orders;

medium

A. The date format string is not enclosed in quotes

B. TO_CHAR cannot be used on dates

C. Missing FROM clause

D. TO_CHAR requires three arguments

Solution

1. Step 1: Check TO_CHAR syntax

   The format string must be enclosed in single quotes, like 'YYYY-MM-DD'.

2. Step 2: Identify the error in the query

   The query uses YYYY-MM-DD without quotes, causing a syntax error.

3. Final Answer:

   The date format string is not enclosed in quotes -> Option A

4. Quick Check:

   Format strings need quotes in TO_CHAR [OK]

Hint: Always quote format strings in TO_CHAR [OK]

Common Mistakes:

• Forgetting quotes around format strings
• Thinking TO_CHAR needs more arguments
• Ignoring syntax errors from missing quotes

5. You want to find the average order amount rounded to 2 decimal places from the orders table. Which query correctly achieves this in Snowflake?

hard

A. SELECT AVG(ROUND(order_amount, 2)) FROM orders;

B. SELECT AVG(CAST(order_amount AS NUMBER(10,2))) FROM orders;

C. SELECT TO_CHAR(AVG(order_amount), '0.00') FROM orders;

D. SELECT ROUND(AVG(order_amount), 2) FROM orders;

Solution

1. Step 1: Understand rounding average correctly

   Rounding the average after calculating it gives the correct average rounded to 2 decimals.

2. Step 2: Analyze each option

   SELECT ROUND(AVG(order_amount), 2) FROM orders; rounds the AVG result, which is correct. SELECT AVG(ROUND(order_amount, 2)) FROM orders; rounds each value before averaging, which changes the result. SELECT TO_CHAR(AVG(order_amount), '0.00') FROM orders; converts to string, not numeric. SELECT AVG(CAST(order_amount AS NUMBER(10,2))) FROM orders; casts each value before averaging, which changes the result.

3. Final Answer:

   SELECT ROUND(AVG(order_amount), 2) FROM orders; -> Option D

4. Quick Check:

   Round after AVG for correct decimal rounding [OK]

Hint: Round the average, not individual values [OK]

Common Mistakes:

• Rounding before averaging changes results
• Using TO_CHAR instead of numeric rounding
• Casting individuals before averaging changes results