SQL, RETENTION RATE

Question

Помогите решить задачу :
подсчитайте Retention Rate. Отобразите три поля: 
когорту; 
месяц; 
Retention Rate в процентах с округлением до двух знаков после запятой. 
Отсортируйте таблицу по когорте и по месяцу.

КОД ОТ ПРЕДЫДУЩЕЙ ЗАДАЧИ:

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;

Mail ovushka · Answer

Для расчета Retention Rate с округлением до двух знаков после запятой и сортировки по когорте и месяцу, нужно изменить запрос следующим образом: 
 
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' 
) 
, 
cohort_counts AS ( 
SELECT first_purchase_month, 
purchase_month, 
COUNT(DISTINCT user_id) AS unique_users 
FROM cohort_data 
GROUP BY first_purchase_month, purchase_month 
) 
, 
cohort_retention AS ( 
SELECT first_purchase_month, 
purchase_month, 
unique_users, 
first_value(unique_users) OVER (PARTITION BY first_purchase_month ORDER BY purchase_month) AS cohort_size, 
ROUND((unique_users::numeric / first_value(unique_users) OVER (PARTITION BY first_purchase_month ORDER BY purchase_month) * 100), 2) AS retention_rate 
FROM cohort_counts 
) 
SELECT first_purchase_month, purchase_month, retention_rate 
FROM cohort_retention 
ORDER BY first_purchase_month, purchase_month;