generated from yandex-praktikum/de-project-sprint-1
-
Notifications
You must be signed in to change notification settings - Fork 0
/
tmp_rfm_recency.sql
31 lines (30 loc) · 1.03 KB
/
tmp_rfm_recency.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
CREATE TABLE analysis.tmp_rfm_recency (
user_id INT NOT NULL PRIMARY KEY,
recency INT NOT NULL CHECK(recency >= 1 AND recency <= 5)
);
INSERT INTO analysis.tmp_rfm_recency (user_id, recency)
WITH rc AS
(SELECT u.id,
o.status,
o.order_ts,
CASE
WHEN o.status = 4 THEN o.order_ts
ELSE
(SELECT MIN(o2.order_ts)
FROM production.orders o2)
END AS order_time,
row_number() OVER (PARTITION BY u.id
ORDER BY CASE
WHEN o.status = 4 THEN o.order_ts
ELSE
(SELECT MIN(o2.order_ts)
FROM production.orders o2)
END DESC) AS row_number
FROM production.users u
LEFT JOIN production.orders o ON u.id = o.user_id
ORDER BY u.id ASC)
SELECT rc.id,
ntile(5) OVER (
ORDER BY rc.order_time) AS recency
FROM rc
WHERE rc.row_number = 1;