Interview Question Bank · LAG · ROW_NUMBER · skipping NULLs
7. Latest performance rating fell
MediumHRQuestion #187
Concept: LAG · ROW_NUMBER · skipping NULLsshow
LAG(x) OVER (PARTITION BY person ORDER BY date) puts each row next to the same person's previous one. To compare only the most recent pair, also number each person's rows newest-first with ROW_NUMBER and keep row 1. Rows that aren't final yet (a NULL rating) must be filtered out before the window runs, or they become the 'latest' row and hide the real comparison.
Example (different data):
WITH r AS (
SELECT runner_id, race_date, finish_minutes,
LAG(finish_minutes) OVER (PARTITION BY runner_id ORDER BY race_date) AS prev_minutes,
ROW_NUMBER() OVER (PARTITION BY runner_id ORDER BY race_date DESC) AS rn
FROM race_results
WHERE finish_minutes IS NOT NULL
)
SELECT * FROM r WHERE rn = 1 AND finish_minutes > prev_minutes;Your task
Before calibration meetings, Quillmoor's managers want a list of people whose most recent finalized rating went down compared with the one before it. Reviews still in progress have a NULL rating.
Using finalized reviews only, return name, latest_cycle (review_cycle of their latest finalized review), previous_rating and latest_rating for employees whose latest rating is lower than their previous one. Sort by the size of the drop (largest first), then name.
Starting Postgres in your browser…
Checking your access…