Interview Question Bank · LAG with offsets · consecutive declines
4. Balance down three months in a row
HardBankingQuestion #107
Concept: LAG with offsets · consecutive declinesshow
LAG(x, n) OVER (PARTITION BY key ORDER BY t) reads the value n rows back in the same partition, so one row can see several earlier ones at once. 'Fell three months in a row' needs four month-ends: this month below last month, last month below the one before, and that one below the one before it. The first rows of each partition get NULL from LAG, and a comparison with NULL is never true, so they drop out on their own.
Example (different data):
SELECT sensor_id, read_at, temp
FROM (
SELECT sensor_id, read_at, temp,
LAG(temp, 1) OVER w AS t1,
LAG(temp, 2) OVER w AS t2
FROM readings
WINDOW w AS (PARTITION BY sensor_id ORDER BY read_at)
) r
WHERE temp > t1 AND t1 > t2;Your task
Copperleaf Bank's retention team wants to call customers whose savings are steadily draining, before they close the account.
In fin_monthly_balances, a month is a decline when its balance is lower than the same account's previous month-end (equal is not a decline). Return account_id, alert_month (the month_end) and balance for every month that completes at least three declines in a row, meaning that month and the two before it were all declines. Sort by account_id, alert_month.
Starting Postgres in your browser…
Checking your access…