SQLRepPath
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960
00/60 solved

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…