Главная

Рассчитайте аналог Retention Rate по месяцам для компаний-заказчиков. Объедините компании в когорты по месяцу их первого заказа (поле order_date). Возвращение определяйте по наличию заказа в текущем месяце. Перед тем как выделить неделю из даты, приведите значения к типу timestamp. Значение Retention Rate округлите до двух знаков после запятой. 

WITH 
    t_1 AS (SELECT c.order_id,
                   c.customer_id,
                   c.cohort_dt, 
                   COUNT(c.order_id) OVER(PARTITION BY c.cohort_dt) AS cohort_users_cnt
            FROM (SELECT DISTINCT order_id, 
                         customer_id,
                         FIRST_VALUE(DATE_TRUNC('month', order_date)) OVER 
                           (PARTITION BY order_id ORDER BY DATE_TRUNC('month', order_date))::timestamp AS cohort_dt
                  FROM northwind.orders) AS c
                 ),
    t_2 AS (SELECT order_id,
                   customer_id,
                   DATE_TRUNC('month', order_date)::timestamp AS purchase_date
            FROM northwind.orders
            ORDER BY customer_id)

SELECT DISTINCT cohort_dt,
        purchase_date,
        COUNT(t_2.order_id) OVER(PARTITION BY t_1.cohort_dt, t_2.purchase_date) AS users_cnt, 
        cohort_users_cnt, 
        ROUND(COUNT(t_2.order_id) OVER(PARTITION BY t_1.cohort_dt, t_2.purchase_date)::numeric / cohort_users_cnt * 100, 2) AS retention_rate
FROM t_2 
JOIN t_1 ON t_1.customer_id = t_2.customer_id
ORDER BY t_1.cohort_dt, t_2.purchase_date