Optimizing Large Join Queries | Dataford Interview Questions

How do you optimize complex queries that involve joining multi-million row tables?

Provide a PostgreSQL query for the report below and explain the performance decisions you would validate with EXPLAIN (ANALYZE, BUFFERS). The result should preserve matters with no matching events.

Output

One row per qualifying matter, with matter_id, organization_name, qualifying_event_count, and latest_activity_at
Include open or paused matters for enterprise organizations created before 2025-04-01
Count only review and filing events during Q1 2025, while latest_activity_at considers all Q1 events
Sort by qualifying event count descending, latest activity descending with nulls last, then matter ID ascending

Schema

organizations

Column	Type	Description
organization_idPK	INT	Unique organization identifier
organization_name	VARCHAR(120)	Organization display name
region	VARCHAR(40)	Primary operating region
segment	VARCHAR(30)	Organization commercial segment

matters

Column	Type	Description
matter_idPK	INT	Unique matter identifier
organization_id	INT	Owning organization identifier
matter_title	VARCHAR(160)	Matter title
status	VARCHAR(20)	Current matter status
created_at	DATE	Matter creation date

matter_events

Column	Type	Description
event_idPK	INT	Unique event identifier
matter_id	INT	Related matter identifier
event_type	VARCHAR(30)	Event classification
event_at	TIMESTAMP	Event timestamp

Your question is Optimizing Large Join Queries. 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