Interview Question Bank · LAG with a gap check · strict thresholds
11. Accounts whose usage halved
HardSaaSQuestion #119
Concept: LAG with a gap check · strict thresholdsshow
LAG returns the previous row, which isn't always the previous period: if a month is missing, it quietly compares with two months ago. Pull the previous row's date too (LAG(month)) and check it is exactly one period back, or self-join on month = previous month + INTERVAL '1 month'. Thresholds like 'more than half' are strict, so test < 0.5, not <= 0.5.
Example (different data):
WITH r AS (
SELECT store_id, week, sales,
LAG(week) OVER w AS prev_week, LAG(sales) OVER w AS prev_sales
FROM weekly_sales
WINDOW w AS (PARTITION BY store_id ORDER BY week)
)
SELECT * FROM r
WHERE prev_week = week - 7 AND sales > prev_sales * 2;Your task
Tallyboard's account managers want an early-warning list: accounts whose API usage fell by more than half from one month to the next. A missing month means usage wasn't metered, so there's nothing to compare across a gap.
Return name, month (YYYY-MM), previous_calls (api_calls in the calendar month just before), api_calls and drop_pct (100 * (previous_calls - api_calls) / previous_calls, rounded to 1 decimal) for every account-month where api_calls is less than half of the previous calendar month's. Sort by month, then name.
Starting Postgres in your browser…
Checking your access…