Modeling changes to daily/weekly/monthly active users

[joellabes]

@LifesGood

What if someone logs in again?

Do you mean multiple times in the same day? The initial table is at the grain of user and day:

@claire

Our data

Let’s say we have a table with one record per user, per day they were active:

user_id	date_day	is_active
1	2020-08-01	true
1	2020-09-01	true
1	2020-09-04	true
1	2020-09-05	true
1	2020-09-12	true
1	2020-09-17	true
2	2020-09-01	true

This line:

@claire

min(date_day) as first_active_day

Is to facilitate the date spine - by doing an inequality join you can get a row for every day even if they didn’t log in. See Finding active days for a subscription/user/account (date spining) for a more in depth discussion