Drill 8 of 8 in Social media questions
0

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…