SQL: Duplicate Rows and Top Percent | Dataford Interview Questions

Your question is SQL: Duplicate Rows and Top Percent. Start with the requirements and the two 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.

Problem

Altimetrik's Workforce Directory team needs a validation query for employee extracts. Using the tables below, write PostgreSQL SQL that produces both a deliberately duplicated employee row and the first 50% of eligible workforce records.

Requirements

Join employees to departments and retain only active employees with a matching department.
Return the row for employee_id = 104 exactly twice using UNION ALL.
Return the first half of eligible employees, ordered by ascending employee_id. Use NTILE(2) so the query remains data-driven.
Add an operation column with duplicated or first_half, and sort the final result by operation and employee ID.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_id	INT	Referenced department identifier
employment_status	VARCHAR(20)	Current employment status
hire_date	DATE	Date the employee joined
email	VARCHAR(150)	Work email address

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	Department display name