Interview Question Bank · generate_series · rolling window join · COUNT(DISTINCT)
7. Monthly actives over a two-month window
HardSocial mediaQuestion #260
Concept: generate_series · rolling window join · COUNT(DISTINCT)show
A rolling metric like 'active this month or last month' can't come from GROUP BY month, because one activity row has to count in two months. Generate the months you report, then join every activity row inside each month's window: on or after the 1st of the previous month and before the 1st of the next. Count DISTINCT users, since someone active in both months appears twice. The generated calendar also keeps months with no activity of their own.
Example (different data):
SELECT d.day::date, COUNT(DISTINCT o.customer_id) AS buyers_7d FROM generate_series(TIMESTAMP '2025-02-01', TIMESTAMP '2025-02-07', INTERVAL '1 day') AS d(day) LEFT JOIN orders o ON o.ordered_on > d.day - INTERVAL '7 days' AND o.ordered_on < d.day + INTERVAL '1 day' GROUP BY d.day ORDER BY d.day;
Your task
Loopline counts a user as active in a month if they opened the app that month or the month before, which smooths out people who check in every few weeks. Activity logging was broken for all of April 2025, so there are no April rows.
For each month from January to May 2025, return month (YYYY-MM), users_this_month (distinct users with activity in that calendar month) and active_users (distinct users with activity in that month or the one before). Show every month, even one with no activity. Sort by month.
Starting Postgres in your browser…
Checking your access…