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 usage
  (user_id int, time_stamp TIMESTAMP, activity_type varchar);

INSERT INTO usage
  (user_id, time_stamp, activity_type)
VALUES
  (1, '2022-01-08 04:05:06', 'read'),
  (2, '2022-01-01 04:05:06', 'comment'),
  (3, '2022-01-05 04:05:06', 'like'),
  (3, '2022-01-05 04:05:06', 'post'),
  (1, '2022-01-06 04:05:06', 'read'),
  (4, '2022-01-02 04:05:06', 'read'),
  (3, '2022-01-03 04:05:06', 'comment'),
  (2, '2022-01-04 04:05:06', 'sign-in'),
  (1, '2022-03-05 04:05:06', 'post')
;
9 rows affected

-- out of order - need a window function with ORDER BY
WITH prev_time_tbl AS (
    SELECT 
        *,
        LEAD(time_stamp) OVER(PARTITION BY user_id ORDER BY time_stamp DESC) AS prev_time
    FROM usage
), churn_or_not_tbl AS (
    SELECT 
        user_id,
        CASE WHEN 
            (time_stamp::date - prev_time::date >= 7)
        THEN 1 
        ELSE 0 END churn_or_not
    FROM prev_time_tbl
    ORDER BY user_id, churn_or_not DESC
)
SELECT 
    SUM(churn_or_not) AS "# churned in 7 days ever"
FROM churn_or_not_tbl

    


# churned in 7 days ever


1