Optimizing Slow SQL Queries | Dataford Interview Questions

Explain the process of optimizing a slow-running SQL query at Pinterest.

Using the provided PostgreSQL schema, explain how you would investigate and improve the query, then provide the optimized query. Address execution plans, filtering, joins, aggregation, and relevant indexing decisions.

Output

One row per qualifying creator with creator_id, creator_name, and qualifying_saves
Include only published pins with saves from January 2025 and at least two qualifying saves
Order by qualifying_saves descending, then creator_id ascending

Schema

creators

Column	Type	Description
creator_idPK	INT	Unique Pinterest creator identifier
creator_name	VARCHAR(100)	Display name of the creator

pins

Column	Type	Description
pin_idPK	INT	Unique pin identifier
creator_id	INT	Creator who published the pin
status	VARCHAR(20)	Current pin publication status

pin_saves

Column	Type	Description
save_idPK	INT	Unique save event identifier
pin_id	INT	Pin that was saved
saved_at	TIMESTAMP	Timestamp when the pin was saved

Your question is Optimizing Slow SQL 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