Когортный анализ LTV: практика

Question

SELECT user_id, DATE_TRUNC('MONTH', created_at)::date AS month_start, COUNT(*) OVER (PARTITION BY DATE_TRUNC('MONTH', created_at)) AS cohort_size FROM tools_shop.users ORDER BY month_start; Поместите предыдущий запрос в общее табличное выражение. Постройте таблицу с сессиями, где будут находиться следующие поля: лайфтайм; месяц заказа; месяц старта когорты; количество пользователей в когорте, или размер когорты; выручка. Подсказка Чтобы выделить месяц, используйте функцию DATE_TRUNC, а затем приведите результат к типу date. Для расчёта лайфтайма заказа используйте комбинацию функций EXTRACT и AGE. Функция AGE(<дата_1>, <дата_2>) вернёт разницу между двумя датами в виде интервала времени, а EXTRACT с аргументом MONTH позволит получить количество месяцев из этого интервала. Группировать значения не нужно. Чтобы добавить все необходимые поля, присоедините к таблице из предыдущего задания таблицу с заказами.

Ekaterina · Answer

Здравствуйте!

Вам удалось решить? У меня при таком запросе пишет, что мало строк:

WITH cohort_users AS ( 
    SELECT 
        user_id, 
        DATE_TRUNC('month', created_at)::date AS cohort_month, 
        COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', created_at)::date) AS cohort_users_count 
    FROM 
        tools_shop.users 
), 
 
session_revenue AS ( 
    SELECT 
        DATE_TRUNC('day', orders.created_at)::date AS order_day, 
        cu.cohort_month AS cohort_month, 
        cu.cohort_users_count, 
        SUM( orders.total _amt) AS revenue 
    FROM 
        tools_shop.orders 
    JOIN 
        cohort_users cu ON orders.user_id = cu.user_id 
    GROUP BY 
        order_day, cu.cohort_month, cu.cohort_users_count 
) 
 
SELECT 
    EXTRACT(month FROM AGE(order_day, cohort_month)) AS lifetime, 
    order_day AS order_month, 
    cohort_month, 
    cohort_users_count, 
    revenue 
FROM 
    session_revenue 
ORDER BY 
    order_day, lifetime;

Евелина Бладина · Answer

WITH cohort_users AS (  
  -- Подзапрос для определения когорт пользователей 
  SELECT  
    user_id,  
    DATE_TRUNC('month', created_at)::date AS cohort_month,  
    COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', created_at)) AS cohort_size  
  FROM tools_shop.users  
),  
 
orders_with_cohort AS (  
  -- Подзапрос для объединения данных о заказах с информацией о когортах 
  SELECT  
    o.user_id,  
     o.total _amt,  
    DATE_TRUNC('month', o.created_at)::date AS order_month,  
    cu.cohort_month,  
    cu.cohort_size  
  FROM tools_shop.orders o  
  JOIN cohort_users cu ON cu.user_id = o.user_id  
)  
 
SELECT  
  -- Вычисление продолжительности жизни заказа в месяцах 
  EXTRACT(MONTH FROM AGE(o.order_month, cu.cohort_month)) AS lifetime,  
  o.order_month,  
  cu.cohort_month AS cohort_start_month,  
  cu.cohort_size AS cohort_users_count,  
   o.total _amt AS revenue  
FROM orders_with_cohort o  
JOIN cohort_users cu ON cu.user_id = o.user_id  
ORDER BY cohort_start_month, order_month;