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
32
33
34
35
| WITH user_dates AS (
SELECT
uid,
MIN(DATE(in_time)) AS first_day,
MAX(DATE(out_time)) AS last_day
FROM tb_userlog
GROUP BY uid
),
user_grades AS (
SELECT
uid,
CASE
WHEN first_day >= DATE_SUB('2021-11-04', INTERVAL 6 DAY) THEN '新晋用户'
ELSE
CASE
WHEN last_day >= DATE_SUB('2021-11-04', INTERVAL 6 DAY) THEN '忠实用户'
ELSE
CASE
WHEN last_day >= DATE_SUB('2021-11-04', INTERVAL 29 DAY) THEN '沉睡用户'
ELSE '流失用户'
END
END
END AS user_grade
FROM user_dates
),
total AS (
SELECT COUNT(*) AS total_users
FROM user_grades
)
SELECT
user_grade,
ROUND(COUNT(*) / total_users, 2) AS ratio
FROM user_grades, total
GROUP BY user_grade, total_users
ORDER BY ratio DESC;
|
