Users Retained Month Over Month
Problem
An app logs every session in activity(user_id, day), with day as a YYYY-MM-DD date, and a user can have many sessions in a month. A user is active in a month if they have at least one session in it, and retained in a month if they are active in it and were also active in the calendar month right before it. For every month that has activity, return month as YYYY-MM, the number of active users and the number of retained users, sorted by month.
Examples
Input: activity = [(1, "2026-01-05"), (1, "2026-01-20"), (2, "2026-01-09"), (1, "2026-02-02"), (3, "2026-02-14"), (2, "2026-03-01"), (3, "2026-03-30")]
Output: [("2026-01", 2, 0), ("2026-02", 2, 1), ("2026-03", 2, 1)]
Why: user 1 is retained in February and user 3 in March; user 2 skipped February, so March does not count themInput: activity = [(1, "2026-04-10"), (1, "2026-06-10")]
Output: [("2026-04", 1, 0), ("2026-06", 1, 0)]
Why: May has no activity, so it gets no row, and June's previous month is May, not AprilInput: activity = [(7, "2025-12-31"), (7, "2026-01-01"), (8, "2026-01-15")]
Output: [("2025-12", 1, 0), ("2026-01", 2, 1)]
Why: edge case, the month before January is December of the previous yearHints
0 / 3
Sessions are the wrong grain. First reduce the log to one row per user per active month, then the question is about those rows.
Put that reduction in a CTE named, say, monthly. A user is retained in a month when monthly also has a row for the same user in the previous month, which is a join of monthly to itself.
LEFT JOIN monthly AS prev on the same user_id and prev.month = the month before cur.month; in SQLite that is strftime('%Y-%m', cur.month || '-01', '-1 month'). Then GROUP BY cur.month: COUNT(*) is active and COUNT(prev.user_id) counts only the rows that found a match.
Solution
The CTE monthly turns the raw log into the grain the question is asked at, one row per user per active month, and DISTINCT is what makes several sessions in a month count once. The main query reads that CTE twice, as the current month and as the month before, which is exactly what a named result is for. The join is a LEFT JOIN so that users who were not active the month before still count as active; COUNT(*) counts every current row, while COUNT(prev.user_id) skips the NULLs the left join leaves behind and so counts only retained users. Computing the previous month with date arithmetic rather than string tricks handles the December to January step and treats a gap month correctly. Building the CTE is O(n log n), and the self-join is a lookup per row on at most n rows.
import sqlite3
QUERY = """
WITH monthly AS (
SELECT DISTINCT user_id, strftime('%Y-%m', day) AS month
FROM activity
)
SELECT cur.month,
COUNT(*) AS active,
COUNT(prev.user_id) AS retained
FROM monthly AS cur
LEFT JOIN monthly AS prev
ON prev.user_id = cur.user_id
AND prev.month = strftime('%Y-%m', cur.month || '-01', '-1 month')
GROUP BY cur.month
ORDER BY cur.month
"""
def run(rows):
db = sqlite3.connect(":memory:")
db.execute("CREATE TABLE activity (user_id INTEGER, day TEXT)")
db.executemany("INSERT INTO activity VALUES (?, ?)", rows)
return db.execute(QUERY).fetchall()
print(run([(1, "2026-01-05"), (1, "2026-01-20"), (2, "2026-01-09"), (1, "2026-02-02"), (3, "2026-02-14"), (2, "2026-03-01"), (3, "2026-03-30")])) # -> [('2026-01', 2, 0), ('2026-02', 2, 1), ('2026-03', 2, 1)]
print(run([(1, "2026-04-10"), (1, "2026-06-10")])) # -> [('2026-04', 1, 0), ('2026-06', 1, 0)]
print(run([(7, "2025-12-31"), (7, "2026-01-01"), (8, "2026-01-15")])) # -> [('2025-12', 1, 0), ('2026-01', 2, 1)]Stuck on the idea rather than the code? CTEs and Recursion covers it.