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

Question

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

Антон Чирков Чирков Ученик (99), на голосовании 3 недели назад

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 позволит получить количество месяцев из этого интервала. Группировать значения не нужно. Чтобы добавить все необходимые поля, присоедините к таблице из предыдущего задания таблицу с заказами.

Answer 1

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

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

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;