CASE with aggregate functions in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count how many orders have a total price greater than 100.

SQL

SELECT COUNT(CASE WHEN total_price [1] 100 THEN 1 END) AS high_value_orders FROM orders;

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

A>

B<=

C=

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' will count orders less than 100.

Using '=' will only count orders exactly 100.

The query counts orders where total_price is greater than 100 using a CASE inside COUNT.

2fill in blank

medium

Complete the code to sum the total_price only for orders where status is 'completed'.

SQL

SELECT SUM(CASE WHEN status = 'completed' THEN [1] ELSE 0 END) AS completed_total FROM orders;

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

A0

B1

Cstatus

Dtotal_price

Attempts:

3 left

💡 Hint

Common Mistakes

Returning 1 counts orders but does not sum prices.

Returning status is incorrect because it's a string, not a number.

The CASE returns total_price for completed orders, else 0, so SUM adds only completed orders' prices.

3fill in blank

hard

Fix the error in the query to count orders with discount applied (discount > 0).

SQL

SELECT COUNT(CASE WHEN discount [1] 0 THEN 1 END) AS discounted_orders FROM orders;

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

A<=

B>

C==

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '==' causes syntax error in SQL.

Using '<=' counts orders with no discount or zero discount.

SQL uses a single > for greater than, not ==. So use discount > 0 to count discounted orders.

4fill in blank

hard

Fill both blanks to calculate average price for 'shipped' orders only.

SQL

SELECT AVG(CASE WHEN status [1] 'shipped' THEN [2] ELSE NULL END) AS avg_shipped_price FROM orders;

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

A=

Btotal_price

Cstatus

D!=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '!=' will average orders not shipped.

Returning status instead of total_price gives wrong data type.

The CASE checks if status equals 'shipped' and returns total_price to average only those orders.

5fill in blank

hard

Fill all three blanks to count orders with priority 'high' and total_price over 200.

SQL

SELECT COUNT(CASE WHEN priority [1] 'high' AND total_price [2] [3] THEN 1 END) AS high_value_high_priority FROM orders;

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

A=

B>

C200

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' reverses the condition.

Using wrong value for price threshold causes wrong count.

The CASE counts orders where priority equals 'high' and total_price is greater than 200.