Partitioning and Indexing | Dataford Interview Questions

What strategies do you use for partitioning and indexing large datasets to improve query performance?

Demonstrate the strategy with a PostgreSQL query that summarizes completed orders during the first quarter of 2026 by store. The query should preserve stores with no matching orders and use a date range suitable for partition pruning.

Output

One row per store with store_id, store_name, and completed_order_value
Include every store, using zero when no qualifying orders exist
Sort by completed_order_value descending, then store_id ascending

Schema

orders

Column	Type	Description
order_idPK	BIGINT	Unique order identifier
store_id	INT	Store that received the order
order_date	DATE	Order creation date and partitioning key
status	VARCHAR(20)	Order lifecycle status
order_total	NUMERIC(12,2)	Total order value

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Store display name
region	VARCHAR(30)	Operating region

Your question is Partitioning and Indexing. 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