Расчёт Retention Rate: практика. Часть 2.Task #1-5

--1.Создайте таблицу, где будут пользователи, зарегистрированные в январе 2020 года. Кроме того, отобразите ещё одно поле, где будет представлено общее количество этих пользователей.
SELECT user_id, COUNT(*) OVER(PARTITION BY DATE_TRUNC('month', created_at)::date)
FROM tools_shop.users
WHERE DATE_TRUNC('month', created_at)::date = '2020-01-01'

--2.Отобразите все записи с событиями с платформы android таблицы tools_shop.events с января по март 2020 года включительно.
SELECT *
FROM tools_shop.events
WHERE platform = 'android' AND DATE_TRUNC('day', event_time)::date BETWEEN '2020-01-01' AND '2020-03-31'

--3.Объедините таблицы, полученные в предыдущих запросах, по полю user_id.Важно! Укажите во FROM сначала таблицу с событиями и присоединяйте к ней таблицу с пользователями, но не наоборот.
WITH users_mnt AS (
          SELECT user_id, COUNT(*) OVER(PARTITION BY DATE_TRUNC('month', created_at)::date)
          FROM tools_shop.users
          WHERE DATE_TRUNC('month', created_at)::date = '2020-01-01'),
     events_mnt AS (
          SELECT *
          FROM tools_shop.events
          WHERE platform = 'android' AND DATE_TRUNC('day', event_time)::date BETWEEN '2020-01-01' AND '2020-03-31')
SELECT *
FROM events_mnt e JOIN users_mnt u ON e.user_id = u.user_id

--4.Сгруппируйте данные по месяцу события (в виде '2020-01-01 00:00:00') и количеству зарегистрированных пользователей. Посчитайте число уникальных пользователей в таблице tools_shop.events. В итоговой таблице должно быть три поля:
месяц события,
общее количество зарегистрированных пользователей,
число уникальных вернувшихся пользователей по месяцам.
Отсортируйте таблицу по двум полям — по месяцу события и количеству зарегистрированных пользователей.
WITH profiles as (
     SELECT user_id,
     COUNT(user_id) OVER () AS user_cnt FROM tools_shop.users
     WHERE (created_at::date) BETWEEN '2020-01-01' AND '2020-01-31'
),
events as (
    SELECT *
    FROM tools_shop.events
    WHERE ((event_time::date) BETWEEN '2020-01-01' AND '2020-03-31')
    AND platform = 'android')
SELECT CAST(DATE_TRUNC('month', event_time)as TIMESTAMP) as month,
       user_cnt,
       COUNT(DISTINCT events.user_id) AS cnt_user
FROM events JOIN profiles ON events.user_id = profiles.user_id
GROUP BY month, user_cnt
ORDER BY month, user_cnt

--5.Вместо подсчёта количества пользователей подсчитайте долю. Не забудьте перевести делимое в тип numeric для корректного деления.
WITH profiles as (
     SELECT user_id,
     COUNT(user_id) OVER () AS user_cnt FROM tools_shop.users
     WHERE (created_at::date) BETWEEN '2020-01-01' AND '2020-01-31'
),
events as (
    SELECT *
    FROM tools_shop.events
    WHERE ((event_time::date) BETWEEN '2020-01-01' AND '2020-03-31')
    AND platform = 'android')
SELECT CAST(DATE_TRUNC('month', event_time)as TIMESTAMP) as month,
       user_cnt,
       COUNT(DISTINCT events.user_id)::numeric / user_cnt::numeric AS rr_user
FROM events JOIN profiles ON events.user_id = profiles.user_id
GROUP BY month, user_cnt
ORDER BY month, user_cnt