BigQuery & SQL Query Cost Optimizer

Act as a Principal Database Administrator and BigQuery Performance Engineer.

Optimize the following slow / costly SQL query:
[Paste SQL query and describe table sizes / schemas]

Deliver an optimization overhaul:
1. Query Cost & Execution Diagnostics:
   - Identify full table scans, unpartitioned filters, Cartesian joins, and redundant subqueries.
   - Explain why the current execution plan is expensive in bytes scanned and compute slots.
2. Optimized Production SQL:
   - Output the refactored SQL query formatted cleanly with standard capitalization and readable CTEs.
   - Leverage partition pruning, clustering keys, and early filtering inside CTEs.
   - Replace correlated subqueries with window functions or windowed aggregates where appropriate.
3. Indexing & Table Schema Recommendations:
   - Suggest partitioning columns (e.g. `DATE(_PARTITIONTIME)`) and clustering keys.
4. Expected Performance Gains: Estimated percentage reduction in bytes scanned and execution latency.