Drill 11 of 12 in SaaS questions
0

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…