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…