Organizational Hierarchy with Tenure
UptoSkills Arena is trying to understand employee loyalty and career progression. They want to analyze how long employees have been with the company, and how this compares to their manager's tenure. This analysis is crucial for mentorship program effectiveness.
Given the following tables:
employees(employee_id INT PRIMARY KEY, name VARCHAR(100) NOT NULL, hire_date DATE NOT NULL, manager_id INT)companies(company_id INT PRIMARY KEY, founding_date DATE NOT NULL)
Return:
employee_name: The name of the employee.hire_year: The year the employee was hired.company_founding_year: The year the company was founded.employee_tenure_years: The total number of full years the employee has worked for the company.manager_tenure_years: The total number of full years the employee's direct manager has worked for the company. For employees without a manager, this should be NULL.
Order the results by employee_tenure_years descending, then by employee_name ascending. Handle ties in tenure by ordering alphabetically by name. NULLs should appear last for manager_tenure_years.
Tables
CREATE TABLE companies (
company_id INTEGER PRIMARY KEY,
founding_date DATE NOT NULL
);
CREATE TABLE employees (
employee_id INTEGER PRIMARY KEY,
name VARCHAR(100) NOT NULL,
hire_date DATE NOT NULL,
manager_id INTEGER
);Example data
INSERT INTO companies (company_id, founding_date) VALUES (1, '2005-01-01'); INSERT INTO employees (employee_id, name, hire_date, manager_id) VALUES (101, 'Alice', '2010-05-15', NULL), (102, 'Bob', '2012-08-20', 101), (103, 'Charlie', '2011-03-10', 101), (104, 'David', '2015-11-25', 102), (105, 'Eve', '2013-07-01', 102), (106, 'Frank', '2018-01-30', 103), (107, 'Grace', '2019-09-01', 104);
Your query is also checked on hidden datasets with different data.
Hints
-- Calculate employee and manager tenures
SELECT
e.name AS employee_name,
EXTRACT(YEAR FROM e.hire_date) AS hire_year,
EXTRACT(YEAR FROM c.founding_date) AS company_founding_year,
DATE_PART('year', AGE(e.hire_date)) AS employee_tenure_years,
NULL AS manager_tenure_years -- Placeholder
FROM
employees e
JOIN
companies c ON true
Your query runs on the example tables shown with the problem, then on hidden datasets when you submit.
Write standard SQL. MySQL habits work too: IFNULL, IF, YEAR(), DATE_FORMAT, GROUP_CONCAT, `backticks` and LIMIT 5, 10. It runs on PostgreSQL, so integer division keeps whole numbers (5 / 2 = 2): write 5.0 / 2 for 2.5.