Interview Question Bank · Monthly SUM · LAG · consecutive flags
8. Logins halved three months running
HardSocial mediaQuestion #261
Concept: Monthly SUM · LAG · consecutive flagsshow
Period-over-period questions take two steps: aggregate to one row per entity per period (date_trunc('month', d) with SUM), then compare neighbours with LAG(value) OVER (PARTITION BY entity ORDER BY period). For 'N periods in a row', compute a true/false flag per period, then look back at the flag itself with LAG(flag, 1) and LAG(flag, 2). Writing 'less than half' as value * 2 < previous keeps integer math exact.
Example (different data):
WITH w AS (
SELECT store_id, date_trunc('week', sold_on)::date AS week, SUM(units) AS units
FROM sales GROUP BY 1, 2
), f AS (
SELECT *, units > LAG(units) OVER (PARTITION BY store_id ORDER BY week) AS grew
FROM w
)
SELECT store_id, week
FROM (SELECT *, LAG(grew) OVER (PARTITION BY store_id ORDER BY week) AS grew_before FROM f) x
WHERE grew AND grew_before;Your task
Loopline's retention team wants to reach users who are drifting away fast: those whose monthly logins fell by more than half three months in a row. Every user in bt_logins logged in at least once in each month from January to May 2025.
Total each user's logins per calendar month. A month 'fell' if its total is less than half of the same user's previous month. For every month that fell where the two months before it also fell, return user_id, month (YYYY-MM), logins (that month's total) and start_logins (the total three months earlier). Sort by month, then user_id.
Starting Postgres in your browser…
Checking your access…