Days employed as of 2026-01-01

SQL data engineering interview problem. Difficulty: intermediate. Pattern: Date Functions. About 12 minutes. Part of the Pro drill bank.

HR is preparing a report on how long each person has worked at the company, up to the start of 2026. Instead of using today's date, the report always uses the fixed date 1 January 2026, so the result never changes. For each employee in employees, work out the number of days between hire_date and DATE '2026-01-01'. Return name, hire_date and days_employed. Sort by days_employed from the largest, then by name. Do not use CURRENT_DATE.

Requirements

• Calculate days_employed as the number of days from hire_date to 2026-01-01.
• Use the fixed date DATE '2026-01-01', not the current date.
• Return name, hire_date and days_employed.
• Sort by days_employed descending, then name.

Constraints

• The table is employees.
• Subtracting two dates, DATE '2026-01-01' - hire_date, gives a number of days in this database.
• The as-of date must be DATE '2026-01-01'.

Examples

Input: name | hire_date Omar Ali | 2020-06-01 Eve Ng | 2024-01-05 Output: name | hire_date | days_employed Omar Ali | 2020-06-01 | 2040 Eve Ng | 2024-01-05 | 727 Why this passes: As-of date is fixed at 2026-01-01 so grading stays deterministic.

Topics: lakebench, sql, dates, datediff.

More SQL interview questions · All interview problems · Learn data engineering