Drill 1 of 12 in HR questions
0

Interview Question Bank · CASE · INTERVAL anniversaries · custom sort

1. Tenure buckets for current staff

EasyHRQuestion #181

Concept: CASE · INTERVAL anniversaries · custom sortshow

CASE turns a continuous value into labelled buckets: the first WHEN that matches wins, so order the branches from one end of the scale to the other. For tenure, compare the hire date with the snapshot date minus an INTERVAL ('1 year', '2 years'), which lands on real anniversaries; dividing a day count by 365 drifts across leap days. Labels sort alphabetically, so order the groups by something on the real scale, like MIN(hire_date).

Example (different data):

SELECT CASE WHEN joined_on > DATE '2025-01-01' - INTERVAL '6 months' THEN 'New'
            ELSE 'Established' END AS member_type,
  COUNT(*) AS members
FROM gym_members
GROUP BY 1
ORDER BY MIN(joined_on) DESC;

Your task

Quillmoor, a logistics software company founded in 2023, is preparing a retention report as of 2025-06-30 and wants to see how its current staff splits by tenure.

For current employees (termination_date IS NULL), return tenure_bucket ('Under 1 year', '1-2 years' or '2+ years', measuring from hire_date to 2025-06-30, where reaching an exact anniversary counts) and employees. Sort from the shortest tenure bucket to the longest.

Starting Postgres in your browser…

Checking your access…