Optimizing SQL and Data Pipelines | Dataford Interview Questions

Tell me how you would optimize a slow-running SQL query or a memory-intensive data pipeline used in JPMorgan Chase data platforms.

Assume you are reviewing a PostgreSQL workload that joins query telemetry with pipeline run metadata. Write a SQL query that returns the worst-performing jobs by average runtime and memory usage, so an engineer can prioritize tuning work.

Output

One row per job name.
Columns: job_name, avg_runtime_ms, avg_peak_memory_mb, slow_run_count.
Include only jobs with at least 2 runs and an average runtime above 1000 ms.
Ignore rows where peak_memory_mb is NULL.
Sort by avg_runtime_ms descending, then avg_peak_memory_mb descending, then job_name ascending.

Schema

jobs

Column	Type	Description
job_idPK	INT	Unique job identifier
job_name	VARCHAR(100)	Human-readable job name
platform	VARCHAR(50)	JPMorgan Chase data platform name

pipeline_runs

Column	Type	Description
run_idPK	INT	Unique run identifier
job_id	INT	References jobs.job_id
run_date	DATE	Date the run executed
runtime_ms	NUMERIC(12,2)	Total runtime in milliseconds
peak_memory_mb	NUMERIC(12,2)	Peak memory usage in megabytes

Your question is Optimizing SQL and Data Pipelines. 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