Write a complex MySQL query that involves multiple joins, aggregations, and subqueries, and explain how you would optimize its execution plan.
Use PostgreSQL-compatible SQL for this exercise. Produce a monthly provider report for active providers with sent surveys during 2025, and discuss indexing, cardinality, pre-aggregation, and plan inspection.
Output
- One row per active provider and survey month with sent invitations.
- Columns:
organization_name, provider_name, survey_month, invitations, completed_responses, average_score, issue_count, response_rate_pct, and monthly_response_rank.
- Include months with no completed responses, sort by
survey_month, monthly_response_rank, and provider_name.
