Pivot Rows into Columns

A classic interview question: for each customer, how many orders are paid, shipped and cancelled? One row per customer, one column per status. In the orders table, the status is a value in a single column, one row per order. Turning those values into columns is called a pivot.

The idea in one sentence: a CASE turns each row into 1 or 0, and the SUM of those numbers counts only the rows you picked, so each status gets its own SUM(CASE …) column. For example, on a votes table with one row per vote, the yes and no votes of each poll:

SELECT poll,
    SUM(CASE WHEN answer = 'yes' THEN 1 ELSE 0 END) AS yes_votes,
    SUM(CASE WHEN answer = 'no' THEN 1 ELSE 0 END) AS no_votes
FROM votes
GROUP BY poll

Use SUM, not COUNT: a 0 is still a value, so COUNT would count every row. A LEFT JOIN keeps Eva, who never ordered: her one joined row has NULL in every order column, so each CASE gives her 0, and COUNT(orders.id), which skips NULLs, gives her a total of 0 (COUNT(*) would say 1).

Your query prints:

name | paid | shipped | cancelled | total
Ana Pop | 2 | 0 | 0 | 2
Ben Ortiz | 1 | 0 | 1 | 2
Chloe Ng | 1 | 1 | 0 | 2
Dan Weber | 1 | 0 | 0 | 1
Eva Rossi | 0 | 0 | 0 | 0
(5 rows)

Your task: in query, list every customer's name with a paid, a shipped and a cancelled column and the total of their orders, from customers left-joined with orders, one group per customer id.

Assignment
Follow the Coding Tutorial to build the query one clause per line: first the name and the total, then FROM, the LEFT JOIN and the GROUP BY, and last the three status columns.

Hint
Each status column is SUM(CASE WHEN status = '…' THEN 1 ELSE 0 END) with its own name and a comma at the end of the line, because total comes after it.