If I have rows containing user information, including two timestamp values contains 'created' and 'deleted' data (where 'deleted' is null if the account is active), is there a way to count active users on a given day (i.e. between those two values)?
Example:
| id | created | deleted |
|---|---|---|
| 01 | 2022-05-01 | 2022-08-01 |
| 02 | 2022-06-01 | 2022-08-13 |
| 03 | 2022-07-01 | NULL |
What I would like returned:
| date | active_user_count |
|---|---|
| 2022-05-01 | 1 |
| 2022-06-01 | 2 |
| 2022-07-01 | 3 |
| 2022-08-01 | 2 |
| 2022-08-25 | 1 |
The main idea is to count created as +1 and deleted as -1.
In your data sample each date appears only once, but in a real life scenario we can expect multiple entries (created/deleted) in a single day. Therefore, we first need to calculate the daily sum of the entries and then calculate a running total.
select dt
,sum(sum(delta)) over(order by dt) as active_user_count
from
( select id, created as dt, 1 as delta from t
union all select id, deleted, -1 from t where deleted is not null
) t
group by dt
| dt | active_user_count |
|---|---|
| 2022-05-01 00:00:00 | 1 |
| 2022-06-01 00:00:00 | 2 |
| 2022-07-01 00:00:00 | 3 |
| 2022-08-01 00:00:00 | 2 |
| 2022-08-13 00:00:00 | 1 |