Drill 4 of 12 in HR questions
0

Interview Question Bank · DISTINCT ON · as-of filter · employment check

4. Salary in effect on a given date

MediumHRQuestion #184

Concept: DISTINCT ON · as-of filter · employment checkshow

A history table holds one row per change, so 'the value on date X' is the latest row on or before X. Filter effective_date <= X first, then keep each entity's newest remaining row with DISTINCT ON (entity) ... ORDER BY entity, effective_date DESC. Skip the filter and you pick up changes made after X, including ones scheduled for the future; use MAX(value) and you get the highest value, which isn't always the latest.

Example (different data):

SELECT DISTINCT ON (product_id) product_id, price, valid_from
FROM product_prices
WHERE valid_from <= DATE '2025-03-31'
ORDER BY product_id, valid_from DESC;

Your task

Quillmoor's comp review compares this year's raises with what each engineer earned at the start of the year. hr_salary_history only covers Engineering so far, and each row applies from its effective_date until the next one.

For every engineer employed on 2025-01-01 (hired by then and not terminated before it), return name, effective_date and salary of the history row in effect on 2025-01-01. Sort by salary descending, then name.

Starting Postgres in your browser…

Checking your access…