Describe how you would optimize a slow-running query that joins multiple large event tables containing billions of communication logs at Twilio.
Provide an optimized PostgreSQL query and explain the main performance decisions. Use the supplied tables and restrict processing to communication events from 2025-01-01 through 2025-01-03.
Output
- One row per account and communication day.
- Columns:
account_id, account_name, event_day, messages_sent, delivered_events, failed_events, and total_cost.
- Include only accounts with messages in the date range. Sort by
account_id, then event_day.
Output details
- Treat the date range as inclusive of
2025-01-01 and 2025-01-03, excluding timestamps on or after 2025-01-04.
- Return missing delivery counts and costs as
0, and format total_cost with exactly two decimal places.
