Rolling cohort retention SQL

01-signed_up_users.sql

SELECT count(1) as signed_up_users
FROM users
WHERE users.sign_up_time >= date_trunc('day', now() - interval '2 days')
AND users.sign_up_time < date_trunc('day', now() - interval '1 day');

02-logged_in_users.sql

SELECT count(distinct events.user_id) as logged_in_users
FROM events
WHERE events.type = 'login' 
AND events.time >= date_trunc('day', now() - interval '1 day')
AND events.time < date_trunc('day', now());

03-single_day_retention.sql

SELECT
count(distinct users.id) as signed_up_users,
count(distinct events.user_id) as logged_in_users
FROM users
LEFT JOIN events
ON users.id = events.user_id
AND events.type = 'login'
AND events.time >= '2013-01-04'
AND events.time < '2013-01-05'
WHERE users.sign_up_time >= '2013-01-03'
AND users.sign_up_time < '2013-01-04';

04-join_example.txt

SELECT * from users LEFT JOIN events ON users.id = events.user_id LIMIT 5;

 id  |    sign_up_time     | id  | user_id | type  |        time         
-----+---------------------+-----+---------+-------+---------------------
 651 | 2013-01-05 13:38:22 |     |         |       | 
 652 | 2013-01-05 14:04:13 |     |         |       | 
 653 | 2013-01-05 14:16:36 | 363 |     653 | login | 2013-01-06 12:16:36
 653 | 2013-01-05 14:16:36 | 362 |     653 | login | 2013-01-06 12:16:36
 654 | 2013-01-05 14:19:21 | 364 |     654 | login | 2013-01-06 12:19:21

05-rolling_retention.sql

SELECT
date_trunc('day', users.sign_up_time) as day,
count(distinct users.id) as signed_up_users,
count(distinct events.user_id) as logged_in_users
FROM users
LEFT JOIN events
ON users.id = events.user_id
AND events.time >= date_trunc('day', users.sign_up_time + interval '1 day')
AND events.time < date_trunc('day', users.sign_up_time + interval '2 days')
WHERE users.sign_up_time >= '2013-01-03'
AND users.sign_up_time < '2013-01-13'
GROUP BY 1
ORDER BY 1;