Analytics Patterns · First-seen month · running distinct count
56. New vs returning users
Concept: First-seen month · running distinct countshow
A user is new in the month of their first-ever activity and returning in every later month they show up. Postgres doesn't allow COUNT(DISTINCT ...) OVER (...), so a running count of distinct users is built from first-seen months: count new users per month, then take a running SUM of those counts.
Example (different data):
WITH firsts AS ( SELECT reader_id, MIN(read_on) AS first_read FROM article_reads GROUP BY reader_id ) SELECT first_read, COUNT(*) AS new_readers, SUM(COUNT(*)) OVER (ORDER BY first_read) AS total_readers FROM firsts GROUP BY first_read;
Your task
The growth team's monthly report splits active users into new and returning, and tracks how many people have ever used the app.
Using app_opens, for each month return month (YYYY-MM), new_users (active that month for the first time ever), returning_users (active that month, first active in an earlier month) and cumulative_users (distinct users seen in any month up to and including this one). Oldest first.
Starting Postgres in your browser…
Checking your access…