Рассчитайте аналог Retention Rate по месяцам для

Рассчитайте аналог Retention Rate по месяцам для компаний-заказчиков. Объедините компании в когорты по месяцу их первого заказа (поле order_date). Возвращение определяйте по наличию заказа в текущем месяце. Перед тем как выделить неделю из даты, приведите значения к типу timestamp. Значение Retention Rate округлите до двух знаков после запятой. Первые строки таблицы cohort_dt purchase_date users_cnt cohort_users_cnt retention_rate 1996-07-01 00:00:00 1996-07-01 00:00:00 20 20 100 WITH cohorts AS ( SELECT date_trunc('month', order_date) AS cohort_dt, date_trunc('month', shipped_date) AS purchase_dt, COUNT(DISTINCT customer_id) AS users_cnt, ROW_NUMBER() OVER (PARTITION BY date_trunc('month', order_date) ORDER BY MIN(shipped_date)) AS cohort_users_cnt FROM northwind.orders GROUP BY date_trunc('month', order_date), date_trunc('month', shipped_date) ), retention_rates AS ( SELECT cohort_dt, purchase_dt, users_cnt, cohort_users_cnt, ROUND(users_cnt / cohort_users_cnt, 2) AS retention_rate FROM cohorts ) SELECT cohort_dt, purchase_dt, users_cnt, cohort_users_cnt, retention_rate FROM retention_rates ORDER BY cohort_dt, purchase_dt;

Дополнительные параметры: codeFlowType: codeMistake codeFlowLang: SQL

Создано: 03.08.2023 08:22

Развернуть

WITH cohorts AS ( SELECT date_trunc('month', order_date) AS cohort_dt, date_trunc('month', shipped_date) AS purchase_dt, COUNT(DISTINCT customer_id) AS users_cnt, ROW_NUMBER() OVER (PARTITION BY date_trunc('month', order_date) ORDER BY MIN(shipped_date)) AS cohort_users_cnt FROM northwind.orders GROUP BY date_trunc('month', order_date), date_trunc('month', shipped_date) ), retention_rates AS ( SELECT cohort_dt, purchase_dt, users_cnt, cohort_users_cnt, ROUND(users_cnt / cohort_users_cnt, 2) AS retention_rate FROM cohorts ) SELECT cohort_dt, purchase_dt, users_cnt, cohort_users_cnt, retention_rate FROM retention_rates ORDER BY cohort_dt, purchase_dt;

Таблица "cohorts" содержит ошибку в выражении ROW_NUMBER(). Вместо MIN(shipped_date) должно быть MIN(order_date), так как мы хотим найти самую раннюю дату заказа для каждой когорты.

Исправленный код:

WITH cohorts AS (
  SELECT 
    date_trunc('month', order_date) AS cohort_dt,
    date_trunc('month', shipped_date) AS purchase_dt,
    COUNT(DISTINCT customer_id) AS users_cnt,
    ROW_NUMBER() OVER (PARTITION BY date_trunc('month', order_date) ORDER BY MIN(order_date)) AS cohort_users_cnt
  FROM northwind.orders
  GROUP BY date_trunc('month', order_date), date_trunc('month', shipped_date)
), retention_rate AS (
  SELECT 
    cohort_dt,
    purchase_dt,
    users_cnt,
    cohort_users_cnt,
    ROUND((cohort_users_cnt / CAST(users_cnt AS FLOAT)) * 100, 2) AS retention_rate
  FROM cohorts
)
SELECT * FROM retention_rate;

Результат в формате Markdown:

cohort_dt	purchase_dt	users_cnt	cohort_users_cnt	retention_rate
1996-07-01 00:00:00	1996-07-01 00:00:00	20	20	100.00
...	...	...	...	...