Layout

One row per customer, all statuses

PROBLEM 9 / 24 · BASICAll problems ↗

For every customer, including those with no orders, return customer_id, completed_count, cancelled_count, and pending_count. Missing counts must be zero. Row order does not matter.

Outputcustomer_idcompleted_countcancelled_countpending_count

query.sql

DuckDB SQL

Restoring your saved draft…

⌘ / Ctrl + Enter to run

Run a query to see results.

Reveal solution & explanation
SELECT c.customer_id, SUM(CASE WHEN o.status = 'completed' THEN 1 ELSE 0 END) AS completed_count, SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_count, SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) AS pending_count FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id GROUP BY c.customer_id;

A WHERE condition on the unmatched order would turn the LEFT JOIN into an effective inner join. Conditional sums preserve the customer population.

Data & schema

customer_id
INTEGER · PK
customer_name
VARCHAR
country
VARCHAR
email
VARCHAR
signup_date
DATE
Table notes & relationships

Buyer accounts. Some buyers have no orders, and some email addresses are NULL.

Fictional teaching data. Money is USD cents; timestamps are UTC.