Conditional aggregation pattern in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count the number of orders with status 'shipped'.

SQL

SELECT COUNT(CASE WHEN status = [1] THEN 1 END) AS shipped_count FROM orders;

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

A'pending'

Bshipped

C'shipped'

D'delivered'

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to use quotes around string values.

Using the wrong status value.

The status value must be a string literal, so it needs to be enclosed in single quotes.

2fill in blank

medium

Complete the code to sum the total amount for orders with status 'completed'.

SQL

SELECT SUM(CASE WHEN status = [1] THEN amount ELSE 0 END) AS total_completed FROM orders;

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

A'completed'

B'cancelled'

C'shipped'

D'pending'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong status value.

Not using quotes around the status string.

We want to sum amounts only for orders where status is 'completed', so use 'completed' with quotes.

3fill in blank

hard

Fix the error in the code to count orders with status 'pending'.

SQL

SELECT COUNT(CASE WHEN status = [1] THEN 1 END) AS pending_count FROM orders;

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

A'pending'

B'Pending'

Cpending

D"pending"

Attempts:

3 left

💡 Hint

Common Mistakes

Using no quotes or double quotes for string literals.

Using incorrect capitalization.

String literals in SQL must be enclosed in single quotes, so use 'pending'.

4fill in blank

hard

Fill both blanks to calculate the count of 'shipped' and 'cancelled' orders separately.

SQL

SELECT COUNT(CASE WHEN status = [1] THEN 1 END) AS shipped_count, COUNT(CASE WHEN status = [2] THEN 1 END) AS cancelled_count FROM orders;

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

A'shipped'

B'completed'

C'cancelled'

D'pending'

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up the status values.

Forgetting quotes around string literals.

We count orders with status 'shipped' and 'cancelled', so use those exact string literals.

5fill in blank

hard

Fill all three blanks to sum amounts for 'completed' orders, count 'pending' orders, and count 'cancelled' orders.

SQL

SELECT SUM(CASE WHEN status = [1] THEN amount ELSE 0 END) AS total_completed, COUNT(CASE WHEN status = [2] THEN 1 END) AS pending_count, COUNT(CASE WHEN status = [3] THEN 1 END) AS cancelled_count FROM orders;

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

A'completed'

B'pending'

C'cancelled'

D'shipped'

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong status values for each aggregation.

Omitting quotes around string literals.

We sum amounts for 'completed' orders, count 'pending' orders, and count 'cancelled' orders, so use those exact string literals.