Interview Question Bank · SUM OVER (RANGE BETWEEN INTERVAL ... PRECEDING) · CASE
9. Loyalty tier from trailing 12-month nights
HardHospitalityQuestion #216
Concept: SUM OVER (RANGE BETWEEN INTERVAL ... PRECEDING) · CASEshow
A trailing total over time needs a window frame measured in time, not in rows: SUM(x) OVER (PARTITION BY person ORDER BY date RANGE BETWEEN INTERVAL '12 months' PRECEDING AND CURRENT ROW) adds every row dated from 12 months back through the current one, however many rows that is. ROWS counts rows instead, and an unframed ORDER BY gives an all-time running total. INTERVAL '12 months' follows the calendar, while 365 days comes up one day short across a leap day. Then a CASE turns the total into a tier.
Example (different data):
SELECT member_id, visit_date, points,
SUM(points) OVER (PARTITION BY member_id ORDER BY visit_date
RANGE BETWEEN INTERVAL '6 months' PRECEDING AND CURRENT ROW) AS points_6m
FROM gym_visits;Your task
Tidewell Hotels' loyalty program sets a guest's tier from the nights they've stayed in the past 12 months. The program team wants to see the tier each guest reached at every checkout.
For every completed stay (status 'stayed'), nights is check_out - check_in. Compute trailing_nights: the guest's nights from stays whose check_out falls in the 12 months up to this stay's check_out, counting a stay that checked out exactly 12 months earlier and this stay itself. tier is 'Gold' at 15 or more trailing nights, 'Silver' at 8 to 14 and 'Member' below 8. Return guest (name), check_out, nights, trailing_nights and tier. Sort by guest, then check_out.
Starting Postgres in your browser…
Checking your access…