Optimizing Poorly Performing SQL | Dataford Interview Questions

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

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

Schema

engagements

Column	Type	Description
engagement_idPK	INT	Unique engagement identifier
client_name	VARCHAR(100)	Client associated with the engagement
status	VARCHAR(20)	Current engagement status

engagement_events

Column	Type	Description
event_idPK	INT	Unique event identifier
engagement_id	INT	Related engagement
event_at	TIMESTAMP	Timestamp when the event occurred
event_type	VARCHAR(30)	Event classification
hours	DECIMAL(8,2)	Hours attributed to the event

engagement_reviews

Column	Type	Description
review_idPK	INT	Unique review identifier
engagement_id	INT	Reviewed engagement
reviewed_at	TIMESTAMP	Timestamp when the review was completed
review_status	VARCHAR(30)	Review outcome

Your question is Optimizing Poorly Performing SQL. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem