只要想到怎样去区分每一等级用户，那这题目做起来就会比较顺利。本题的思路是使用每一位用户的最晚登录时间、

最早登录时间与今天的日期间间隔的天数来区分每一等级用户，具体的做法见sql分析。

1. 临时表user_min_max_time中存储每位用户最早登录时间、最晚登录时间、今天的日期。

    SELECT
        uid,
        MAX(in_time) OVER(PARTITION BY uid) max_time, # 最晚登录时间
        MIN(in_time) OVER(PARTITION BY uid) min_time,# 最早登录时间
        MAX(in_time) OVER() all_max_time # 今天的日期
    FROM
        tb_user_log

2. 区分每一等级用户

SELECT
                DISTINCT uid,
                CASE 
                WHEN DATEDIFF(all_max_time,min_time) >= 0 AND 
                DATEDIFF(all_max_time,min_time) <= 6  # 最早登录时间在7天之内
                THEN 
                    '新晋用户'
                WHEN DATEDIFF(all_max_time,min_time) > 6 AND 
                DATEDIFF(all_max_time,max_time) >= 0 AND 
                DATEDIFF(all_max_time,max_time) <= 6     # 最早登录时间不在7天之内，且最晚登录时间在7天之内           
                              THEN 
                    '忠实用户'
                WHEN  DATEDIFF(all_max_time,max_time) > 6 AND
                DATEDIFF(all_max_time,max_time) < 30 # 最晚登录时间不在7天之内，但在30之内
                THEN
                    '沉睡用户'
                WHEN DATEDIFF(all_max_time,max_time) >= 30 # 最晚登录时间不在30天之内
                THEN 
                    '流失用户'
                END user_grade
            FROM
                user_min_max_time

3. 计算每一等级用户的数量

SELECT 
            user_grade,
            COUNT(uid) grade_user_cnt
        FROM(
            SELECT
                DISTINCT uid,
                CASE 
                WHEN DATEDIFF(all_max_time,min_time) >= 0 AND 
                DATEDIFF(all_max_time,min_time) <= 6 
                THEN 
                    '新晋用户'
                WHEN DATEDIFF(all_max_time,min_time) > 6 AND 
                DATEDIFF(all_max_time,max_time) >= 0 AND 
                DATEDIFF(all_max_time,max_time) <= 6
                THEN 
                    '忠实用户'
                WHEN  DATEDIFF(all_max_time,max_time) > 6 AND
                DATEDIFF(all_max_time,max_time) < 30 
                THEN
                    '沉睡用户'
                WHEN DATEDIFF(all_max_time,max_time) >= 30
                THEN 
                    '流失用户'
                END user_grade
            FROM
                user_min_max_time
        )tmp1
        GROUP BY user_grade

4. 计算每一等级用户的占比

SELECT
    user_grade, ROUND(grade_user_cnt / total_users, 2) ratio
FROM(
    SELECT
        user_grade,grade_user_cnt,
        SUM(grade_user_cnt) OVER() total_users
    FROM(
        SELECT 
            user_grade,
            COUNT(uid) grade_user_cnt
        FROM(
            SELECT
                DISTINCT uid,
                CASE 
                WHEN DATEDIFF(all_max_time,min_time) >= 0 AND 
                DATEDIFF(all_max_time,min_time) <= 6 
                THEN 
                    '新晋用户'
                WHEN DATEDIFF(all_max_time,min_time) > 6 AND 
                DATEDIFF(all_max_time,max_time) >= 0 AND 
                DATEDIFF(all_max_time,max_time) <= 6
                THEN 
                    '忠实用户'
                WHEN  DATEDIFF(all_max_time,max_time) > 6 AND
                DATEDIFF(all_max_time,max_time) < 30 
                THEN
                    '沉睡用户'
                WHEN DATEDIFF(all_max_time,max_time) >= 30
                THEN 
                    '流失用户'
                END user_grade
            FROM
                user_min_max_time
        )tmp1
        GROUP BY user_grade
    )tmp2
)tmp3

5. 最终的代码如下：

WITH user_min_max_time AS ( 
    SELECT
        uid,
        MAX(in_time) OVER(PARTITION BY uid) max_time,
        MIN(in_time) OVER(PARTITION BY uid) min_time,
        MAX(in_time) OVER() all_max_time
    FROM
        tb_user_log
)
SELECT
    user_grade, ROUND(grade_user_cnt / total_users, 2) ratio
FROM(
    SELECT
        user_grade,grade_user_cnt,
        SUM(grade_user_cnt) OVER() total_users
    FROM(
        SELECT 
            user_grade,
            COUNT(uid) grade_user_cnt
        FROM(
            SELECT
                DISTINCT uid,
                CASE 
                WHEN DATEDIFF(all_max_time,min_time) >= 0 AND 
                DATEDIFF(all_max_time,min_time) <= 6 
                THEN 
                    '新晋用户'
                WHEN DATEDIFF(all_max_time,min_time) > 6 AND 
                DATEDIFF(all_max_time,max_time) >= 0 AND 
                DATEDIFF(all_max_time,max_time) <= 6
                THEN 
                    '忠实用户'
                WHEN  DATEDIFF(all_max_time,max_time) > 6 AND
                DATEDIFF(all_max_time,max_time) < 30 
                THEN
                    '沉睡用户'
                WHEN DATEDIFF(all_max_time,max_time) >= 30
                THEN 
                    '流失用户'
                END user_grade
            FROM
                user_min_max_time
        )tmp1
        GROUP BY user_grade
    )tmp2
)tmp3
ORDER BY ratio DESC,user_grade;

题解 | #统计活跃间隔对用户分级结果#

全站热榜

创作者周榜