Daily Metrics by Order Status in a Single SQL Pass

You have an orders table with columns: order_id, status (pending/completed/cancelled), amount, date. Compute the following metrics by date: total_revenue: sum of amount for completed orders only. pending_count: count of pending orders. cancelled_count: count of cancelled orders. Requirements: Single SQL query. Aim for “one pass through data” using conditional aggregation. Group results by date. Example Input order_id,status,amount,date 1,completed,10,2024-01-01 2,pending,5,2024-01-01 3,cancelled,7,2024-01-01 4,completed,3,2024-01-02 Output 2024-01-01,total_revenue=10,pending_count=1,cancelled_count=1 2024-01-02,total_revenue=3,pending_count=0,cancelled_count=0