Partitioning for Historical Data | Dataford Interview Questions

How would you use partitioning to improve query performance on large historical datasets in TaskRabbit’s warehouse?

Write a PostgreSQL query against the provided date-partitioned task_events table. Return monthly metrics for the first quarter of 2024, while preserving category-level month-over-month booking value comparisons. The date range must be bounded so historical partitions outside the requested period can be excluded.

Output

One row per category and month with event data in Q1 2024
Columns: category_name, month_start, completed_tasks, total_booking_value, prior_month_value, and value_change
Include zero-value months that have events but no completed tasks
Sort by category_name, then month_start

Schema

task_events

Column	Type	Description
event_idPK	INT	Unique task event identifier
task_id	INT	Task associated with the event
category_id	INT	Task category reference
event_date	DATE	Event date and partitioning column
status	VARCHAR(40)	Task event status
booking_amount	NUMERIC(12,2)	Booking amount associated with the event

task_categories

Column	Type	Description
category_idPK	INT	Unique category identifier
category_name	VARCHAR(80)	Task category name

Your question is Partitioning for Historical Data. 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