Can you explain your approach to optimizing queries within the BigQuery environment?
For this practical exercise, write a PostgreSQL query that returns customer revenue from qualifying 2025 orders. Apply a performance-conscious approach by limiting data to the required period and status before producing the final customer-level results.
Output
- One row per customer with at least 100.00 in qualifying revenue.
- Columns:
customer_id, customer_name, region, total_revenue, and completed_orders.
- Sort by
total_revenue descending, then customer_id ascending.
Output details
- Restrict order dates to
>= DATE '2025-01-01' and < DATE '2026-01-01'.
- Include only orders whose status equals
completed.
- Include only order items with quantity greater than
0.
- Calculate revenue as
quantity * unit_price for each qualifying order item.
- Count each qualifying order once in
completed_orders, regardless of its number of items.