/
IgorFedorov
/
RFM_customer_analysis
Обзор
Документация
Войти
/
IgorFedorov
/
RFM_customer_analysis
Код
Запросы
0
Задачи
Вики
Пакеты
0
Релизы
0
CI/CD
Аналитика
Безопасность
master
SQL/rfm_cumulative.sql
58 строк
1 KB
IgorFedorov
Добавлены файлы в проект.
29 сен 2025, 21:25
29 сен 2025, 21:25
df8d1eb
Код
Авторство
О чём код?
WITH rfm_base AS ( select card, max(datetime)::date AS last_date, count(*) AS frequency, sum(summ - bonus_spent) AS monetary from bonuscheques where length(card) = 13 --[[and {{datetime}}]] group by card having count(*) > 1 ), rfm_calc as ( select card, max(last_date) over() - last_date AS recency, frequency, monetary from rfm_base ), rfm_client as ( select card, case when recency < 18 then '1' when recency < 61 then '2' else '3' end || case when frequency > 5 then '1' when frequency > 2 then '2' else '3' end || case when monetary > 5211 then '1' when monetary > 1390 then '2' else '3' end as rfm from rfm_calc ) select --rfm, case when rfm = '111' then 'A' when rfm IN('112', '113', '121', '122', '123') then 'B' when rfm IN('211', '212', '213', '221', '222') then 'C' when rfm IN('131', '132', '133', '223', '231', '232', '233') then 'D' when rfm IN('311', '312', '313', '321', '322') then 'E' else 'F' end as group_val, count(*) as "Количество" from rfm_client group by 1 order by 1