Gsk2GJpX

1use orders;
2
3/* Определяем критерии для каждой буквы R, F, M:
4 * R = 3 для клиентов, которые покупали <= 30 дней от последней даты в базе,  R = 2 для клиентов, которые покупали > 30 и <= 60 дней от последней даты в базе
5 * F = 3 для клиентов, которые совершили >= 15 покупок, F = 2 для клиентов, которые покупали < 15 раз, но >= 5,  F = 1, если менее 5 раз
6 * M = 3 для клиентов, которые потратили более 30к, M = 2 для клиентов, которые потратили < 30к раз, но >= 10к,  M = 1, если менее 10к
7 */
8
9drop table if EXISTS rfm_data;
10CREATE TEMPORARY table IF NOT EXISTS rfm_data as (
11select user_id, count(user_id) as orders_num, SUM(price) as orders_sum, max(o_date) as last_order FROM orders group by user_id
12);
13
14--  Для каждого пользователя получаем набор из 3 цифр (от 111 до 333, где 333 – самые классные пользователи)
15drop table if EXISTS rfm_data_prep;
16CREATE TEMPORARY table IF NOT exists rfm_data_prep as (
17select 
18user_id,
19orders_num,
20orders_sum,
21last_order,
22case
23	when DATEDIFF((select MAX(o_date) from orders), last_order) <= 30 then '3'
24	when DATEDIFF((select MAX(o_date) from orders), last_order) <= 60 then '2'
25	ELSE '1'
26end
27as R,
28case
29	when orders_num >= 15 then '3'
30	when orders_num >= 5 then '2'
31	ELSE '1'
32end
33as F,
34case
35	when orders_sum >= 30000 then '3'
36	when orders_sum >= 10000 then '2'
37	ELSE '1'
38end
39as M	
40from rfm_data
41);
42
43
44-- удаляем временную таблицу
45drop table if EXISTS rfm_data;
46
47
48-- Вводим группировку - 333 и 233 – это A, 1XX – это C, остальные B
49drop table if EXISTS rfm_data_final;
50CREATE TEMPORARY table IF NOT exists rfm_data_final as (
51select 
52user_id,
53orders_sum,
54case
55	when R = '3' and F = '3' and M = '3' then 'A'
56	when R = '2' and F = '3' and M = '3' then 'A'
57	when R = '1' then 'C'
58	ELSE 'B'
59end
60as cat
61from rfm_data_prep
62);
63
64-- удаляем временнцю таблицу
65drop table if EXISTS rfm_data_prep;
66
67-- Для каждой группы находим кол-во пользователей, кот. попали в них и % товарооборота, которое они сделали за 2 года.
68drop table if EXISTS rfm_check;
69CREATE TEMPORARY table IF NOT exists rfm_check as (
70select 
71cat,
72count(user_id) as users_num,
73sum(orders_sum) as orders_sum,
74sum(orders_sum) / (select sum(price) from orders) * 100 as '% TT'
75from rfm_data_final
76group by cat
77order by cat asc);
78
79select * from rfm_check;
80
81/* Получилось 3 группы:
82 * A - 5195   пользователей,  473934960.9682 у.е, 10.43%
83 * B - 221854 пользователей, 1096600866.0721 у.е, 24.14%
84 * С - 788066 пользователей, 2972151872.5612 у.е, 65.43%
85 */
86
87
88/* Проверяем, что общее кол-во пользователей бьется с суммой кол-во пользователей по группам из п. 3 
89 * То же самое делаем и по деньгам. */
90
91select
92cat,
93users_num,
94orders_sum
95from rfm_check
96union all
97select
98'Итого из rfm таблицы категорий:',
99count(user_id),
100round(sum(orders_sum), 4)
101from rfm_data_final
102union all
103select
104'Итого из изначальной таблицы:',
105count(distinct user_id),
106round(sum(price), 4)
107from orders;
108
109-- Данные сошлись, расчеты выполнены верно.
110
111