CTE vs subquery performance in PostgreSQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a CTE named recent_orders that selects orders from the last 30 days.

PostgreSQL

WITH recent_orders AS (SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '[1] days') SELECT * FROM recent_orders;

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

A7

B60

C15

D30

Attempts:

3 left

💡 Hint

Common Mistakes

Using an interval longer than 30 days changes the result set.

Forgetting to use INTERVAL keyword causes syntax errors.

The CTE filters orders from the last 30 days using the interval '30 days'.

2fill in blank

medium

Complete the code to select customer_id and total order count using a subquery.

PostgreSQL

SELECT customer_id, (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count FROM customers WHERE customer_id = [1];

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

A123

B'abc'

C"123"

D'123'

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around numeric IDs causes type mismatch errors.

Using string values when customer_id is numeric.

The customer_id is a number, so it should be used without quotes in the WHERE clause.

3fill in blank

hard

Fix the error in the CTE query that calculates average order amount per customer.

PostgreSQL

WITH avg_order AS (SELECT customer_id, AVG(order_amount) AS avg_amount FROM orders GROUP BY [1]) SELECT * FROM avg_order;

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

Aorder_id

Bcustomer_id

Corder_date

Dorder_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by order_date returns average per day, not per customer.

Grouping by order_amount causes syntax errors.

The GROUP BY clause must use customer_id to calculate average order amount per customer.

4fill in blank

hard

Fill both blanks to write a query that compares performance by selecting orders using a CTE and a subquery.

PostgreSQL

EXPLAIN ANALYZE WITH recent_orders AS (SELECT * FROM orders WHERE order_date > CURRENT_DATE - INTERVAL '[1] days') SELECT * FROM customers WHERE customer_id IN (SELECT customer_id FROM recent_orders WHERE total > [2]);

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

A30

B100

C50

D7

Attempts:

3 left

💡 Hint

Common Mistakes

Using too large intervals or totals may skew performance results.

Using incorrect interval format causes syntax errors.

The CTE filters recent orders within 30 days, and the subquery filters orders with total greater than 50.

5fill in blank

hard

Fill all three blanks to write a query that uses a CTE to calculate total sales per product, filters by minimum sales, and orders results.

PostgreSQL

WITH sales_summary AS (SELECT product_id, SUM(quantity * price) AS total_sales FROM sales GROUP BY [1]) SELECT product_id, total_sales FROM sales_summary WHERE total_sales > [2] ORDER BY [3] DESC;

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

Aproduct_id

B1000

Ctotal_sales

Dorder_date

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by order_date instead of product_id changes the aggregation.

Ordering by order_date does not sort by sales amount.

The query groups by product_id, filters products with sales over 1000, and orders by total_sales descending.