FILTER clause for conditional aggregation in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to count only the orders with status 'shipped'.

PostgreSQL

SELECT COUNT(*) FILTER (WHERE status = [1]) AS shipped_orders FROM orders;

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

A'pending'

B'shipped'

C'cancelled'

D'returned'

Attempts:

3 left

💡 Hint

Common Mistakes

Using COUNT(status) instead of COUNT(*) with FILTER.

Forgetting to put quotes around the status value.

The FILTER clause counts only rows where the condition is true. Here, it counts orders with status 'shipped'.

2fill in blank

medium

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

PostgreSQL

SELECT SUM(amount) FILTER (WHERE status = [1]) AS completed_total FROM orders;

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

A'completed'

B'pending'

C'shipped'

D'cancelled'

Attempts:

3 left

💡 Hint

Common Mistakes

Summing without FILTER, which sums all amounts.

Using wrong status value in the condition.

The FILTER clause here sums amounts only for orders where status is 'completed'.

3fill in blank

hard

Fix the error in the code to count orders with status 'returned' using FILTER.

PostgreSQL

SELECT COUNT(*) FILTER [1] AS returned_orders FROM orders;

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

A(status = 'returned')

BWHERE status = 'returned'

C(WHERE status = 'returned')

DWHERE (status = 'returned')

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting parentheses after FILTER.

Writing WHERE without parentheses.

The FILTER clause requires parentheses around the WHERE condition.

4fill in blank

hard

Fill both blanks to count orders with status 'pending' and sum amounts for 'completed' orders.

PostgreSQL

SELECT
  COUNT(*) FILTER (WHERE status = [1]) AS pending_count,
  SUM(amount) FILTER (WHERE status = [2]) AS completed_sum
FROM orders;

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

A'pending'

B'completed'

C'shipped'

D'cancelled'

Attempts:

3 left

💡 Hint

Common Mistakes

Swapping the status values between count and sum.

Forgetting quotes around status values.

The first FILTER counts 'pending' orders, the second sums amounts for 'completed' orders.

5fill in blank

hard

Fill all three blanks to count 'shipped' orders, sum 'returned' amounts, and average 'completed' amounts.

PostgreSQL

SELECT
  COUNT(*) FILTER (WHERE status = [1]) AS shipped_count,
  SUM(amount) FILTER (WHERE status = [2]) AS returned_sum,
  AVG(amount) FILTER (WHERE status = [3]) AS completed_avg
FROM orders;

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

A'pending'

B'returned'

C'completed'

D'shipped'

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up status values between aggregates.

Missing quotes around status strings.

Each FILTER clause applies to the correct status: count for 'shipped', sum for 'returned', average for 'completed'.