46. Paid vs Unpaid Invoices

Conditional aggregation counts or sums only the rows that match a condition, without filtering the whole query. There are two idioms: COUNT(CASE WHEN cond THEN 1 END) and COUNT(*) FILTER (WHERE cond) — SQLite (3.45) supports both and they give identical results.

Task: for each client, count how many invoices are paid and how many are unpaid, and sum the amount of the paid ones.

Return columns client, paid_count, unpaid_count, paid_amount, where paid means status = 'paid'. Treat a missing (NULL) paid_amount sum as 0. Order by client ascending.