Optimize PostgreSQL Engagement Reporting Query

How do you optimize a query that is performing poorly on a large dataset?

Using the supplied schema and data, write an efficient PostgreSQL query for the report and explain how you would validate and improve its execution plan. Preserve active engagements even when they have no qualifying events.

Output

1. One row per active engagement with engagement_id, client_name, billable_hours, latest_event_at, and latest_review_status
2. Include billable events from 2025-01-01 through 2025-03-31
3. Order by engagement_id ascending

Output details

• Use event_at >= DATE '2025-01-01' and event_at < DATE '2025-04-01', making the lower bound inclusive and upper bound exclusive.
• Set billable_hours to numeric zero when an active engagement has no qualifying billable events.
• Select each engagement’s latest review before DATE '2025-04-01', ordering by reviewed_at descending and review_id descending for ties.
• Return NULL for latest_event_at and latest_review_status when no qualifying event or review exists.