Partitioning and Indexing Strategy | Dataford Interview Questions

How do you manage partitioning and indexing strategies to improve performance?

Use the provided PostgreSQL schema to demonstrate a time-bounded query against event data. Your solution should return active applications, including those with no matching events, and reflect an indexing strategy that supports the join and time-range filter.

Output

One row per active application, with application_id, event_count, and latest_event_at
Include active applications with zero matching events
Consider events from 2025-01-01 inclusive through 2025-04-01 exclusive
Order by event_count descending, then application_id ascending

Schema

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
status	VARCHAR(20)	Current application status

application_events

Column	Type	Description
event_idPK	INT	Unique event identifier
application_id	INT	Application associated with the event
occurred_at	TIMESTAMP	Event timestamp and partitioning key
event_type	VARCHAR(30)	Type of application event

Your question is Partitioning and Indexing Strategy. Start with the requirements and the two 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