Drill 6 of 12 in HR questions
0

Interview Question Bank · Join · MIN per person · INTERVAL boundary

6. Promoted within two years of hire

MediumHRQuestion #186

Concept: Join · MIN per person · INTERVAL boundaryshow

To test 'within N years of an event', add an INTERVAL to the start date: hire_date + INTERVAL '2 years' lands on the exact anniversary, while + 730 days falls a day short across February 29. Define the event from the data itself (here a higher job level, not just a new title), and collapse to one row per person with MIN when someone qualifies more than once.

Example (different data):

SELECT c.name, MIN(o.ordered_on) AS first_reorder
FROM customers c
JOIN orders o ON o.customer_id = c.id
WHERE o.ordered_on > c.first_order_on
  AND o.ordered_on <= c.first_order_on + INTERVAL '90 days'
GROUP BY c.id, c.name;

Your task

Quillmoor's People team wants to feature fast career growth in its hiring materials: everyone whose first promotion came within two years of being hired, including people who have since left.

A promotion is a job change where to_level is higher than from_level. Return name, hire_date, first_promotion (date of their first promotion) and days_to_promotion (first_promotion - hire_date) for everyone whose first promotion was on or before their second hire anniversary. Sort by days_to_promotion, then name.

Starting Postgres in your browser…

Checking your access…