Drill 9 of 12 in General questions
0

Interview Question Bank · RANGE frame with INTERVAL · per-group MAX

9. Three-month pay total, latest month left out

HardGeneralQuestion #201

Concept: RANGE frame with INTERVAL · per-group MAXshow

ROWS BETWEEN 2 PRECEDING AND CURRENT ROW means the two previous rows, which is only the two previous months when no month is missing. RANGE BETWEEN INTERVAL '2 months' PRECEDING AND CURRENT ROW looks at the values themselves, so a skipped month simply adds nothing. Use MAX(...) OVER (PARTITION BY ...) when each group needs its own cutoff, such as its latest month.

Example (different data):

SELECT store_id, week_start, sales,
  SUM(sales) OVER (PARTITION BY store_id ORDER BY week_start
    RANGE BETWEEN INTERVAL '14 days' PRECEDING AND CURRENT ROW) AS three_week_sales
FROM weekly_sales;

Your task

Hollin & Rook's payroll team is auditing a bonus that depends on each employee's pay over a three-month window. The latest month on each person's record may still be corrected, so it is left out.

For each employee, leaving out their own most recent pay_month, return employee_id, pay_month, amount and rolling_3m (the sum of their pay in that month and the two calendar months before it; months with no row add nothing). Sort by employee_id, then pay_month.

Starting Postgres in your browser…

Checking your access…