Top Longest-Running Assays by Department

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Longest-Running Assays by Department. Start with the requirements and the one table on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

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

Tablesexperimental_runs

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results