Рассчитайте аналог Retention Rate по годам для компаний-покупателей Northwind. Объедините пользователей в когорты по году их первого заказа (поле 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('year', order_date)) OVER 
                           (PARTITION BY order_id ORDER BY DATE_TRUNC('year', order_date))::timestamp AS cohort_dt
                  FROM northwind.orders) AS c
                 ),
    t_2 AS (SELECT order_id,
                   customer_id,
                   DATE_TRUNC('year', 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

Результат

cohort_dt	purchase_date	users_cnt	cohort_users_cnt	retention_rate
1996-01-01 00:00:00	1996-01-01 00:00:00	468	152	307.89
1996-01-01 00:00:00	1997-01-01 00:00:00	943	152	620.39
1996-01-01 00:00:00	1998-01-01 00:00:00	551	152	362.5

Слишком много строк в полученном результате.

: Категория: Я.Практикум Data Science; Просмотров: 1219

Главная

Py_SQL.15/24.1/2.Задача 33