Optimizing Slow Queries | Dataford Interview Questions

Describe a time you had to optimize a slow-running query or a resource-heavy data process.

For this exercise, use the supplied query_runs and analysts tables to produce a concise workload report for January 2025. Include only query fingerprints whose average duration is at least 5,000 milliseconds and that have at least two failed runs.

Output

One row per qualifying query fingerprint, including the analyst name when available.
Columns: query_fingerprint, analyst_name, run_count, avg_duration_ms, and failure_count.
Sort by average duration descending, then query fingerprint ascending.

Schema

query_runs

Column	Type	Description
run_idPK	INT	Unique query execution identifier
query_fingerprint	VARCHAR(80)	Normalized identifier for a query pattern
owner_id	INT	Analyst responsible for the workload
started_at	TIMESTAMP	Timestamp when execution started
duration_ms	INT	Execution duration in milliseconds
status	VARCHAR(20)	Execution outcome

analysts

Column	Type	Description
analyst_idPK	INT	Unique analyst identifier
analyst_name	VARCHAR(100)	Analyst display name
team_name	VARCHAR(80)	Analyst team

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