Churn

WITH profile AS (SELECT user_id,
                         dt AS cohort
                  FROM online_store.profiles
                  WHERE channel = 'Organic'),
     pr_session AS (SELECT user_id,
                           session_start::date AS cnt_date
                    FROM online_store.sessions),
     cohorts AS (SELECT cohort,
                        cnt_date,
                        COUNT(DISTINCT profile.user_id) AS cnt_user
                        FROM profile JOIN pr_session ON profile.user_id = pr_session.user_id
                        GROUP BY cohort, cnt_date)
                        
SELECT *,     
       LAG(cnt_user) OVER (PARTITION BY cohort ORDER BY cnt_date) AS previous_day_users_cnt,
       ROUND((1 - (cnt_user::numeric/ LAG(cnt_user) OVER (PARTITION BY cohort ORDER BY cnt_date))) * 100, 2) AS churn_rate
FROM cohorts;

Другой пример:

WITH
profiles AS (
    -- пользователи совершившие покупку, дата первого события
    SELECT DISTINCT u.user_id,
                    CAST(DATE_TRUNC('month', MIN(e.event_time)) AS date) AS dt
    FROM tools_shop.users AS u
    INNER JOIN tools_shop.orders AS o ON u.user_id=o.user_id
    LEFT JOIN tools_shop.events AS e ON u.user_id=e.user_id
    GROUP BY u.user_id
),

cohorts AS (
    SELECT p.dt as cohort,
           CAST(DATE_TRUNC('month', e.event_time) AS date) AS event_month,
           CAST(COUNT(DISTINCT e.user_id) AS numeric) AS users
    FROM profiles AS p
    LEFT JOIN tools_shop.events AS e ON p.user_id=e.user_id
    GROUP BY p.dt, event_month
)


SELECT cohorts.cohort,
       cohorts.event_month,
       cohorts.users,
       LAG(cohorts.users) OVER(PARTITION BY cohorts.cohort ORDER BY cohorts.event_month) AS prev_month_users,
       ROUND(
           (1 - cohorts.users / LAG(cohorts.users) OVER(PARTITION BY cohorts.cohort ORDER BY cohorts.event_month)) *100
       , 2)
FROM cohorts;