Расчёт Retention Rate: практика.TASK# 1-3

--1.Напишите запрос для определения профиля пользователя. Для этого на экране отобразите три поля: идентификатор пользователя;номер месяца оплаты товара; номер месяца, когда пользователь впервые оплатил товар.
SELECT user_id, EXTRACT('month' FROM paid_at) AS month_paid,
       MIN(EXTRACT('month' FROM paid_at)) OVER(PARTITION BY user_id) AS first_month_paid
FROM tools_shop.orders
WHERE DATE_TRUNC('day', paid_at)::date BETWEEN '2020-01-01' AND '2020-12-31'

--2.Месяц первой покупки играет роль когорты. Кроме того, высчитав месяц покупки, можно считать пользователей. Положите предыдущий код в WITH или подзапрос. Сгруппируйте данные по когорте и месяцу покупки и подсчитайте число уникальных пользователей.
WITH profile AS (
    SELECT user_id, EXTRACT('month' FROM paid_at) AS month_paid,
       MIN(EXTRACT('month' FROM paid_at)) OVER(PARTITION BY user_id) AS cohort
    FROM tools_shop.orders
    WHERE DATE_TRUNC('day', paid_at)::date BETWEEN '2020-01-01' AND '2020-12-31'
)
SELECT cohort, month_paid,
       COUNT(DISTINCT profile.user_id) AS cnt_user
FROM profile
GROUP BY cohort, month_paid

--3.По примеру из теории подсчитайте Retention Rate. Отобразите три поля:когорту;месяц;Retention Rate, округлённый до четырёх знаков после запятой и умноженный на 100. Отсортируйте таблицу по когорте и по месяцу.
WITH profile AS (
    SELECT user_id, EXTRACT('month' FROM paid_at) AS month_paid,
       MIN(EXTRACT('month' FROM paid_at)) OVER(PARTITION BY user_id) AS cohort
    FROM tools_shop.orders
    WHERE DATE_TRUNC('day', paid_at)::date BETWEEN '2020-01-01' AND '2020-12-31'),
    cohorts AS (
        SELECT cohort, month_paid,
               COUNT(DISTINCT profile.user_id) AS cnt_user
        FROM profile
        GROUP BY cohort, month_paid)
SELECT cohort, month_paid,
       ROUND(cnt_user::numeric/MAX(cnt_user) OVER (PARTITION BY cohort)::numeric,4) * 100 AS lk
FROM cohorts
ORDER BY cohort, month_paid