Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 13.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit


  
CREATE TABLE log_data
    (created_at timestamp, user_id int)
;
	
INSERT INTO log_data
    (created_at, user_id)
VALUES
    ('2019-04-04 12:00:00', 425),
    ('2019-03-27 12:00:00', 426),
    ('2019-03-25 12:00:00', 425), 
    ('2019-04-02 12:00:00', 427),
    ('2019-04-02 12:00:00', 425),
    ('2019-03-19 12:00:00', 425),
    ('2019-04-11 12:00:00', 429),
    ('2019-04-06 12:00:00', 423),
    ('2019-04-09 12:00:00', 421),
    ('2019-03-20 12:00:00', 426),
    ('2019-04-13 12:00:00', 423),
    ('2019-04-11 12:00:00', 421)
;
12 rows affected

-- STEPS: 
-- 1. get cohort date and the start of the week, and the start of the week for the previous week for each observation in many dataset
-- 2. count the retained by seeing if 

WITH first_cohort AS (
    SELECT 
        *,
        date_trunc('week', FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at ASC))::DATE AS cohort,
        date_trunc('week', created_at)::DATE AS dt_start_wk,
        COALESCE(LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at ASC), date_trunc('week', FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at ASC))::DATE) AS prev_dt
    FROM log_data
), distinct_users_week AS (
    SELECT 
        cohort,
        dt_start_wk,
        SUM(CASE WHEN 
            (created_at::DATE - prev_dt::DATE) < 7 THEN 1 
            ELSE 0 END) AS retained
    FROM first_cohort
    GROUP BY cohort, dt_start_wk
    ORDER BY cohort ASC, dt_start_wk ASC
), cohort_count AS (
    SELECT 
        cohort,
        COUNT(*) AS n_users
    FROM first_cohort
    GROUP BY cohort
)
SELECT 
    duw.cohort,
    ROW_NUMBER() OVER (PARTITION BY duw.cohort ORDER BY duw.cohort, duw.dt_start_wk) AS retention_week,
    cc.n_users,
    CONCAT(ROUND(duw.retained * 100.0 / cc.n_users,0), '%') AS retention_r
FROM distinct_users_week duw
INNER JOIN cohort_count cc ON duw.cohort = cc.cohort

    


cohort
retention_week
n_users
retention_r


2019-03-18
1
6
33%

2019-03-18
2
6
17%

2019-03-18
3
6
17%

2019-04-01
1
3
67%

2019-04-01
2
3
0%

2019-04-08
1
3
100%


WITH first_cohort AS (
    SELECT 
        *,
        date_trunc('week', FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at ASC))::DATE AS cohort,
        date_trunc('week', created_at)::DATE AS dt_start_wk,
        COALESCE(LAG(created_at) OVER (PARTITION BY user_id ORDER BY created_at ASC), date_trunc('week', FIRST_VALUE(created_at) OVER (PARTITION BY user_id ORDER BY created_at ASC))::DATE) AS prev_dt
    FROM log_data
), distinct_users_week AS (
    SELECT 
        cohort,
        dt_start_wk,
        SUM(CASE WHEN 
            (created_at::DATE - prev_dt::DATE) < 7 THEN 1 
            ELSE 0 END) AS retained
    FROM first_cohort
    GROUP BY cohort, dt_start_wk
    ORDER BY cohort ASC, dt_start_wk ASC
) SELECT * FROM distinct_users_week


cohort
dt_start_wk
retained


2019-03-18
2019-03-18
2

2019-03-18
2019-03-25
1

2019-03-18
2019-04-01
1

2019-04-01
2019-04-01
2

2019-04-01
2019-04-08
0

2019-04-08
2019-04-08
3
cohort	retention_week	n_users	retention_r
2019-03-18	1	6	33%
2019-03-18	2	6	17%
2019-03-18	3	6	17%
2019-04-01	1	3	67%
2019-04-01	2	3	0%
2019-04-08	1	3	100%