Write a SQL query to extract specific metrics from a complex, multi-join schema.
Use the provided schema and sample data. Calculate metrics for each customer from completed orders placed during 2025, while retaining customers with no qualifying orders.
Output
- One row per customer with
customer_id, customer_name, completed_order_count, total_units, and total_revenue.
- Treat missing qualifying orders or line items as zero metrics.
- Order by
total_revenue descending, then completed_order_count descending, then customer_id ascending.
Output details
- Interpret
during 2025 as order dates from 2025-01-01 inclusive through 2026-01-01 exclusive.
- Format
total_revenue as numeric values with exactly two decimal places and total_units and completed_order_count as integers.
- Calculate
total_units by summing line-item quantity and total_revenue by summing quantity * unit_price per qualifying order.
- Count every qualifying completed order, including orders with no line items, while assigning such orders zero units and revenue.
