SQL for Employee Queries

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL for Employee Queries. Start with the requirements and the three tables 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.

Problem

Context

VLink's employee directory needs to identify active employees who are not currently assigned to any department. Department assignments may be expired, cancelled, inactive, or scheduled for a future date.

Task

Write a PostgreSQL query using an as-of date of 2025-02-15 to find all active VLink employees without a valid current department assignment.

Requirements

Consider an assignment current only when its status is ACTIVE, its start date is on or before the as-of date, and its end date is null or on or after the as-of date.
Return each qualifying employee once, even if assignment history contains multiple rows.
Include the employee's most recent recorded department assignment, department hierarchy path, and assignment end date when available.
Exclude employees whose employment status is not ACTIVE.
Use the department hierarchy to produce a readable path from the top-level department to the assigned department.
Sort the results by employee name.

Schema

employees

Column	Type	Description
employee_idPK	INT	Employee identifier
full_name	VARCHAR(100)	Employee full name
email	VARCHAR(150)	Corporate email address
employment_status	VARCHAR(20)	Current employment status

departments

Column	Type	Description
department_idPK	INT	Department identifier
department_name	VARCHAR(100)	Department name
parent_department_id	INT	Parent department identifier

employee_department_assignments

Column	Type	Description
assignment_idPK	INT	Assignment identifier
employee_id	INT	Assigned employee identifier
department_id	INT	Assigned department identifier
assignment_status	VARCHAR(20)	Assignment lifecycle status
effective_start_date	DATE	Assignment start date
effective_end_date	DATE	Assignment end date

Tablesemployeesdepartmentsemployee_department_assignments

Your solutionPostgreSQL

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

Run a query to see results

SQL for Employee Queries

HardSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL for Employee Queries. Start with the requirements and the three tables 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.

Problem

Context

Task

Write a PostgreSQL query using an as-of date of 2025-02-15 to find all active VLink employees without a valid current department assignment.

Requirements

Consider an assignment current only when its status is ACTIVE, its start date is on or before the as-of date, and its end date is null or on or after the as-of date.
Return each qualifying employee once, even if assignment history contains multiple rows.
Include the employee's most recent recorded department assignment, department hierarchy path, and assignment end date when available.
Exclude employees whose employment status is not ACTIVE.
Use the department hierarchy to produce a readable path from the top-level department to the assigned department.
Sort the results by employee name.

Schema

employees

Column	Type	Description
employee_idPK	INT	Employee identifier
full_name	VARCHAR(100)	Employee full name
email	VARCHAR(150)	Corporate email address
employment_status	VARCHAR(20)	Current employment status

departments

Column	Type	Description
department_idPK	INT	Department identifier
department_name	VARCHAR(100)	Department name
parent_department_id	INT	Parent department identifier

employee_department_assignments

Column	Type	Description
assignment_idPK	INT	Assignment identifier
employee_id	INT	Assigned employee identifier
department_id	INT	Assigned department identifier
assignment_status	VARCHAR(20)	Assignment lifecycle status
effective_start_date	DATE	Assignment start date
effective_end_date	DATE	Assignment end date

Tablesemployeesdepartmentsemployee_department_assignments

Your solutionPostgreSQL

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

Run a query to see results