Why CTEs are needed in SQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a CTE named 'recent_orders' that selects orders from 2023.

SQL

WITH [1] AS (SELECT * FROM orders WHERE order_date >= '2023-01-01') SELECT * FROM recent_orders;

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

Arecent_orders

Borders_2022

Cold_orders

Dall_orders

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different CTE name than the one referenced in the main query.

Forgetting to name the CTE.

The CTE name must match the name used later in the query. Here, 'recent_orders' is the correct name.

2fill in blank

medium

Complete the code to select customer_id and total from the CTE named 'customer_totals'.

SQL

WITH customer_totals AS (SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id) SELECT [1] FROM customer_totals;

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

Acustomer_id, total

B*

Camount

Dcustomer_name

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not defined in the CTE.

Using SELECT * when specific columns are asked.

You need to select the columns defined in the CTE: customer_id and total.

3fill in blank

hard

Fix the error in the CTE query by completing the missing keyword.

SQL

WITH recent_orders [1] (SELECT * FROM orders WHERE order_date >= '2023-01-01') SELECT * FROM recent_orders;

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

AWHERE

BSELECT

CAS

DFROM

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting the AS keyword causes syntax errors.

Using SELECT or FROM instead of AS after the CTE name.

The keyword 'AS' is required after the CTE name to define the CTE query.

4fill in blank

hard

Fill both blanks to create a CTE that calculates average sales and then selects customers with sales above 1000.

SQL

WITH avg_sales AS (SELECT customer_id, AVG(amount) [1] sales_avg FROM sales GROUP BY customer_id) SELECT customer_id, sales_avg FROM avg_sales WHERE sales_avg [2] 1000;

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

AAS

B>

C<

DSUM

Attempts:

3 left

💡 Hint

Common Mistakes

Using SUM instead of AVG for average calculation.

Using < instead of > in the WHERE clause.

The alias keyword 'AS' names the average column, and '>' filters customers with sales above 1000.

5fill in blank

hard

Fill all three blanks to create a CTE that finds top products by total sales and selects those with sales over 5000.

SQL

WITH top_products AS (SELECT product_id, [1](quantity * price) [2] total_sales FROM sales GROUP BY product_id) SELECT product_id, total_sales FROM top_products WHERE total_sales [3] 5000;

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

ASUM

BAS

C>

DAVG

Attempts:

3 left

💡 Hint

Common Mistakes

Using AVG instead of SUM for total sales.

Omitting AS keyword.

Using < instead of > in the WHERE clause.

SUM calculates total sales, AS names the column, and > filters products with sales over 5000.