Materialized views for repeated queries in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a materialized view named 'mv_sales_summary'.

Snowflake

CREATE MATERIALIZED VIEW [1] AS SELECT product_id, SUM(sales) AS total_sales FROM sales_data GROUP BY product_id;

Drag options to blanks, or click blank then click option'

Amv_sales_summary

Bsales_summary_view

Csummary_mv

Dsales_mv

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different view name than 'mv_sales_summary'.

Forgetting to specify 'MATERIALIZED' in the view creation.

The materialized view must be named 'mv_sales_summary' as specified.

2fill in blank

medium

Complete the code to refresh the materialized view 'mv_sales_summary'.

Snowflake

ALTER MATERIALIZED VIEW mv_sales_summary [1];

Drag options to blanks, or click blank then click option'

AREFRESH

BRECOMPILE

CUPDATE

DREBUILD

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'REFRESH' which is not valid for materialized views in Snowflake.

Using 'UPDATE' or 'RECOMPILE' which are incorrect commands here.

In Snowflake, to refresh a materialized view, you use the 'REBUILD' command.

3fill in blank

hard

Fix the error in the materialized view creation by completing the missing clause.

Snowflake

CREATE MATERIALIZED VIEW mv_customer_orders AS SELECT customer_id, COUNT(order_id) AS order_count FROM orders [1] customer_id;

Drag options to blanks, or click blank then click option'

AGROUP BY

BORDER BY

CPARTITION BY

DHAVING

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ORDER BY' instead of 'GROUP BY'.

Omitting the grouping clause entirely.

The query needs a 'GROUP BY' clause to aggregate orders by customer_id.

4fill in blank

hard

Fill both blanks to create a materialized view that filters and groups data.

Snowflake

CREATE MATERIALIZED VIEW mv_active_products AS SELECT product_id, SUM(quantity) AS total_quantity FROM sales_data WHERE status = '[1]' [2] product_id;

Drag options to blanks, or click blank then click option'

Aactive

Binactive

CGROUP BY

DORDER BY

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'inactive' instead of 'active' in the filter.

Using 'ORDER BY' instead of 'GROUP BY' for aggregation.

The view filters for 'active' products and groups results by 'product_id'.

5fill in blank

hard

Fill all three blanks to create a materialized view with a filter, grouping, and having clause.

Snowflake

CREATE MATERIALIZED VIEW mv_top_customers AS SELECT customer_id, SUM(amount) AS total_spent FROM transactions WHERE transaction_date >= '[1]' [2] customer_id [3] SUM(amount) > 1000;

Drag options to blanks, or click blank then click option'

A2024-01-01

BGROUP BY

CORDER BY

DHAVING

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ORDER BY' instead of 'HAVING' for filtering.

Omitting the 'GROUP BY' clause.

Using an incorrect date format.

The view filters transactions from 2024-01-01, groups by customer_id, and uses HAVING SUM(amount) > 1000 to show top spenders.