Common Table Expressions (CTEs) in Snowflake - Mini Project: Build & Apply

Common Table Expressions (CTEs) in Snowflake

📖 Scenario: You are working with a sales database in Snowflake. You want to organize your SQL queries better by using Common Table Expressions (CTEs). CTEs help you break down complex queries into smaller, readable parts.

🎯 Goal: Build a SQL query using CTEs to calculate total sales per product category from a sales table.

📋 What You'll Learn

Create a CTE named category_sales that sums sales amounts grouped by product category.

Create a CTE named top_categories that selects categories with total sales greater than 1000.

Write a final SELECT statement that retrieves all columns from top_categories.

Use exact CTE names and SQL syntax as specified.

💡 Why This Matters

🌍 Real World

CTEs help data analysts and engineers write clear and maintainable SQL queries for reporting and data transformation in cloud data warehouses like Snowflake.

💼 Career

Knowing how to use CTEs is essential for roles involving data querying, reporting, and building data pipelines in cloud environments.

1

Create the initial CTE for category sales

Write a CTE named category_sales that selects product_category and the sum of sales_amount as total_sales from the sales table, grouped by product_category.

Snowflake

-- Write the CTE named category_sales here
WITH category_sales AS (
    -- Your code here
)

Need a hint?

Use WITH category_sales AS (SELECT ... FROM sales GROUP BY product_category) to create the CTE.

2

Add a second CTE to filter top categories

Add a second CTE named top_categories that selects all columns from category_sales where total_sales is greater than 1000.

Snowflake

WITH category_sales AS (
    SELECT product_category, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY product_category
),
-- Write the CTE named top_categories here

Need a hint?

Use top_categories AS (SELECT * FROM category_sales WHERE total_sales > 1000) to filter categories.

3

Write the final SELECT statement

Write a final SELECT statement that retrieves all columns from the top_categories CTE.

Snowflake

WITH category_sales AS (
    SELECT product_category, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY product_category
),
top_categories AS (
    SELECT * FROM category_sales WHERE total_sales > 1000
)
-- Write the final SELECT statement here

Need a hint?

Use SELECT * FROM top_categories to get the filtered results.

4

Complete the query with proper formatting

Ensure the entire query uses proper SQL formatting with line breaks and indentation as shown, including the final SELECT statement after the CTEs.

Snowflake

WITH category_sales AS (
    SELECT product_category, SUM(sales_amount) AS total_sales
    FROM sales
    GROUP BY product_category
),
top_categories AS (
    SELECT * FROM category_sales WHERE total_sales > 1000
)
SELECT * FROM top_categories
-- Ensure formatting is correct

Need a hint?

Check that your query matches the formatting with line breaks and indentation.