Context

At Splice, product growth analysis often relies on large event tables such as app activity, subscription changes, and engagement logs. On massive datasets, a correct SQL query can still be unusable if it scans too much data or joins inefficiently.

Core question

Explain how you would optimize a SQL query that is running too slowly on a massive PostgreSQL dataset. In your answer, cover:

1. How you would diagnose the bottleneck before changing the query
2. How you would evaluate joins, filters, aggregations, CTEs, subqueries, and window functions
3. What indexing, partitioning, or data-model changes you might consider
4. How you would validate that the optimized query is both faster and still correct

Scope guidance

The interviewer is looking for a practical, structured explanation rather than generic advice. Focus on PostgreSQL-specific reasoning, trade-offs, and the kinds of query patterns a Product Growth Analyst might use on Splice event and subscription data.