CTE as readable subquery replacement in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start a Common Table Expression (CTE) named recent_orders.

SQL

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

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

Arecent_orders

Borders_recent

Corders

Drecent

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different name for the CTE than in the main query.

Using a table name instead of a CTE name.

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

2fill in blank

medium

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

SQL

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

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

A*

Bcustomer_id

Ccustomer_id, total

Dtotal

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only one column when two are needed.

Using * when specific columns are requested.

To get both customer_id and total columns, list them separated by a comma.

3fill in blank

hard

Fix the error in the CTE definition by completing the code correctly.

SQL

WITH recent_orders AS (SELECT customer_id, SUM(amount) [1] total FROM orders GROUP BY customer_id) SELECT * FROM recent_orders;

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

AAS

BIS

C=

DTO

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' instead of AS for aliasing.

Using 'IS' or 'TO' which are incorrect here.

In SQL, to rename a column or expression, use AS followed by the alias.

4fill in blank

hard

Fill both blanks to create a CTE named top_customers that selects customers with total orders above 1000.

SQL

WITH [1] AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id HAVING total [2] 1000) SELECT * FROM top_customers;

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

Atop_customers

B>

C>=

Dhigh_customers

Attempts:

3 left

💡 Hint

Common Mistakes

Mismatching CTE name and SELECT name.

Using '<' or '<=' instead of '>'.

The CTE must be named top_customers to match the SELECT. The HAVING clause uses '>' to filter totals above 1000.

5fill in blank

hard

Fill all three blanks to create a CTE named recent_customers that selects customer_id and count of orders after 2024-01-01, filtering counts greater than or equal to 5.

SQL

WITH [1] AS (SELECT customer_id, COUNT(*) AS order_count FROM orders WHERE order_date [2] '2024-01-01' GROUP BY customer_id HAVING order_count [3] 5) SELECT * FROM recent_customers;

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

Arecent_customers

B>

C>=

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operators in WHERE or HAVING.

Mismatching CTE name and SELECT name.

The CTE name must be recent_customers. The WHERE clause uses '>' to select orders after the date. The HAVING clause uses '>=' to include customers with at least 5 orders.