Hive sql 常见面试题-统计活跃间隔对用户分级结果

题目需求
用户等级：
忠实用户：近7天活跃且非新用户
新晋用户：近7天新增
沉睡用户：近7天未活跃但是在7天前活跃
流失用户：近30天未活跃但是在30天前活跃
假设今天是数据中所有日期的最大值，从用户登录明细表中的用户登录时间给各用户分级，求出各等级用户的人数
###结果如下：

需要用到的表：
用户登录明细表：user_login_detail

查询sql

WITH tmp as (

    SELECT

        user_id,

        max(date_format(logout_ts,"yyyy-MM-dd")) over() today, --全局窗口

        --按用户id分组的窗口，求出最后一次登录日期

        max(date_format(login_ts,"yyyy-MM-dd")) over(PARTITION by user_id) last_login,

        --按用户id分组的窗口，求出第一次登录日期

        min(date_format(login_ts,"yyyy-MM-dd")) over(PARTITION by user_id) first_login

    from user_login_detail

)

SELECT

	level,

        count(DISTINCT user_id) cn

from(

    SELECT

        user_id,

        case when first_login < date_sub(today,7) AND last_login >= date_sub(today,7) then '忠实用户'

                 when first_login >= date_sub(today,7) then '新增用户' 

                 when last_login < date_sub(today,7) then '沉睡用户'

                 when last_login < date_sub(today,30) then '流失用户'

                 END level

    from tmp 

)t1 group by level