Top Longest-Running Assays by Department | Dataford Interview Questions

Given a table of experimental runs, write a query to find the top three longest-running assays per department using dense rank functions.

Use PostgreSQL and include every assay whose duration rank is 1, 2, or 3 within its department.

Output

Return one row per qualifying experimental run with department, assay_name, duration_minutes, and duration_rank.
Exclude runs with no recorded duration.
Order by department, rank ascending, duration descending, and assay name ascending.

experimental_runs

Column	Type	Description
run_idPK	INT	Unique identifier for the experimental run
department	VARCHAR(100)	Department responsible for the assay
assay_name	VARCHAR(150)	Name of the assay performed
duration_minutes	INT	Elapsed assay duration in minutes

Your question is Top Longest-Running Assays by Department. Start with the requirements and the one table 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.

Given a table of experimental runs, write a query to find the top three longest-running assays per department using dense rank functions.

Use PostgreSQL and include every assay whose duration rank is 1, 2, or 3 within its department.

Return one row per qualifying experimental run with department, assay_name, duration_minutes, and duration_rank.
Exclude runs with no recorded duration.
Order by department, rank ascending, duration descending, and assay name ascending.

experimental_runs

Column	Type	Description
run_idPK	INT	Unique identifier for the experimental run
department	VARCHAR(100)	Department responsible for the assay
assay_name	VARCHAR(150)	Name of the assay performed
duration_minutes	INT	Elapsed assay duration in minutes

Your question is Top Longest-Running Assays by Department. Start with the requirements and the one table 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.