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

Need a 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

Need a 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

Need a 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

Need a hint?

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