Conditional Aggregation (FILTER / CASE)

-- PostgreSQL — FILTER clause (SQL standard, cleanest)
SELECT user_id,
       COUNT(*)                                  AS total_orders,
       COUNT(*) FILTER (WHERE status = 'paid')    AS paid_orders,
       COUNT(*) FILTER (WHERE status = 'pending') AS pending_orders,
       SUM(amount) FILTER (WHERE status = 'paid') AS paid_revenue
FROM   orders
GROUP  BY user_id;

-- Cross-DB — CASE WHEN inside aggregates (works everywhere)
SELECT user_id,
       COUNT(*)                                            AS total_orders,
       SUM(CASE WHEN status = 'paid'    THEN 1 ELSE 0 END) AS paid_orders,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders,
       SUM(CASE WHEN status = 'paid'    THEN amount ELSE 0 END) AS paid_revenue
FROM   orders
GROUP  BY user_id;