SQL RETENTION RATE

Question

SQL RETENTION RATE

Viki Ученик (87), на голосовании 1 год назад

помогите решить задачу SQL:

Месяц первой покупки играет роль когорты. Кроме того, высчитав месяц покупки, можно считать пользователей. Положите предыдущий код в WITH или подзапрос. Сгруппируйте данные по когорте и месяцу покупки и подсчитайте число уникальных пользователей. Первым в группировке должен идти месяц первой покупки, потом месяц покупки. Помните, что считать нужно только уникальных клиентов.
КОД ОТ ПРЕДЫДУЩЕЙ ЗАДАЧИ:

SELECT user_id, EXTRACT(MONTH FROM CAST(paid_at AS date)),
MIN(EXTRACT(MONTH FROM CAST(paid_at AS date))) OVER(PARTITION BY user_id) AS date
FROM tools_shop.orders
WHERE DATE_TRUNC('month', paid_at) BETWEEN '2020-01-01' AND '2020-12-31'

Answer 1

Для решения вашей задачи SQL, вам потребуется использовать подзапрос или выражение WITH для определения когорт и месяца первой покупки. Затем вы сможете сгруппировать данные по когорте и месяцу покупки, подсчитав количество уникальных пользователей.

Вот пример, как можно модифицировать ваш запрос:

WITH first_purchase AS (
SELECT user_id, MIN(EXTRACT(MONTH FROM CAST(paid_at AS date))) AS first_month
FROM tools_shop.orders
WHERE DATE_TRUNC('month', paid_at) BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY user_id
)

SELECT first_month, EXTRACT(MONTH FROM CAST(paid_at AS date)) AS purchase_month, COUNT(DISTINCT user_id) AS unique_users
FROM tools_shop.orders
JOIN first_purchase ON orders.user_id = first_purchase.user_id
WHERE DATE_TRUNC('month', paid_at) BETWEEN '2020-01-01' AND '2020-12-31'
GROUP BY first_month, purchase_month
ORDER BY first_month, purchase_month;

В этом примере мы создали выражение WITH (first_purchase), которое определяет первый месяц покупки для каждого пользователя. Затем мы объединяем таблицу заказов с выражением first_purchase и считаем количество уникальных пользователей для каждой когорты и месяца покупки.

Обратите внимание, что вам может потребоваться изменить имена таблиц и столбцов в соответствии с вашими данными. Также убедитесь, что датафрейм tools_shop.orders содержит все необходимые данные для решения задачи.

Answer 2

 WITH cohort_data AS ( 
    SELECT user_id,  
           EXTRACT(MONTH FROM CAST(paid_at AS date)) AS purchase_month,  
           MIN(EXTRACT(MONTH FROM CAST(paid_at AS date))) OVER(PARTITION BY user_id) AS first_purchase_month 
    FROM tools_shop.orders  
    WHERE DATE_TRUNC('month', paid_at) BETWEEN '2020-01-01' AND '2020-12-31' 
) 
SELECT first_purchase_month,  
       purchase_month,  
       COUNT(DISTINCT user_id) AS unique_users 
FROM cohort_data 
GROUP BY first_purchase_month, purchase_month 
ORDER BY first_purchase_month, purchase_month;

Этот код группирует данные по когорте (месяц первой покупки) и месяцу покупки и подсчитывает число уникальных пользователей в каждой группе. Вы можете использовать эти данные для расчета коэффициента удержания.