Drill 7 of 12 in HR questions
0

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…