Revenue and Order Status Counts

Tesla Online Store operations needs a daily view of order activity. Order revenue is stored at the line-item level, while order status and date are stored in the orders table.

Write a PostgreSQL query to report one row per order date.

Requirements

1. Calculate total_completed_revenue by summing quantity * unit_price only for completed orders.
2. Count pending orders as pending_order_count and cancelled orders as cancelled_order_count.
3. Include dates containing orders without matching line items, showing zero revenue where appropriate.
4. Sort the results chronologically by order_date.

Pre-aggregate line items before joining them to orders so an order with multiple items does not inflate status counts.