SQL Quest › SQL Interview Questions › Aggregation & Grouping

Department Tenure Span

MediumFreeQuerying BasicsAggregation & GroupingDate Functions

HR wants a snapshot of tenure spread per department. For each department, show headcount, earliest_hire (oldest hire_date in the dept), latest_hire (most recent hire_date), and tenure_span_days (difference between latest and earliest, as an integer number of days). Sort by tenure_span_days descending. JULIANDAY converts a date to a day number so you can subtract two dates and get a clean day count.

Solve it in the browser editor →

Runs on SQLite in your browser, graded against the expected result, no signup. A wrong answer gets a diagnosis, not just "incorrect".

Schema

employees

emp_id	name	department	position	salary	hire_date	manager_id	performance_rating
1	Alice Johnson	Engineering	Senior Developer	95000	2019-03-15	5	4.5
2	Bob Smith	Engineering	Developer	75000	2020-06-01	1	3.8
3	Carol Williams	Marketing	Marketing Manager	85000	2018-09-20	NULL	4.2

Expected output: Engineering | 12 | 2018-03-05 | 2024-11-22 | 2454

Hint

MIN(hire_date) and MAX(hire_date) give the earliest and latest. JULIANDAY(MAX(hire_date)) - JULIANDAY(MIN(hire_date)) is the gap in days (as a float). Wrap in CAST(... AS INTEGER) to drop the decimal.

Concepts

SELECT GROUP BY Aggregation Date Functions JULIANDAY

Practise the topic: SQL practice questions · GROUP BY exercises · Date function practice

In these company practice sets

Plaid

A SQL Quest challenge matched to patterns reported for these companies — not a question any of them has published.

Department Tenure Span

Schema

Hint

Concepts

In these company practice sets

Related questions