Hive SQL面试题-流失回流用户数统计

流失回流用户数统计

根据用户最后一次登录记录表，统计每天的流失（一段时间未登录平台）用户数量，和回流用户（一段时间未登录平台，但今天重新登录了平台）数量。
执行环境：Hive on Spark

1 统计指标

从用户最后一次登录记录表中统计如下指标，当日流失用户数量、当日回流用户数量
统计周期指标说明****最近1日流失用户数之前活跃过的用户，最近一段时间未活跃，就称为流失用户。
此处要求统计7日前（只包含7日前当天）活跃，但最近7日未活跃的用户总数。最近1日回流用户数之前的活跃用户，一段时间未活跃（流失），今日又活跃了，就称为回流用户。
此处要求统计回流用户总数。

2 测试数据

• 建表

-- user_id: 用户 id
-- last_login_date: 用户上次登录日期
-- dt: 日期分区
create table user_login_log (
    user_id string,
    last_login_date string,
    dt string
);

• 插入数据

-- 1001 每天登录，普通用户
-- 1002 回流用户，一段时间未登录，然后 2020-06-14 又活跃了
-- 1003 流失用户，从 2020-06-07 之后就没有登录过了
insert into user_login_log values 
('1001', '2020-06-13', '2020-06-13'),
('1001', '2020-06-14', '2020-06-14'),
('1002', '2020-06-01', '2020-06-13'),
('1002', '2020-06-14', '2020-06-14'),
('1003', '2020-06-07', '2020-06-13'),
('1003', '2020-06-07', '2020-06-14');

• 数据样例

select * from user_login_log;

user_idlast_login_datedt10012020-06-132020-06-1310022020-06-012020-06-1310032020-06-072020-06-1310012020-06-142020-06-1410022020-06-142020-06-1410032020-06-072020-06-14

3 实现思路

3.1 流失用户

统计当天（2020-06-14）的流失用户，此类用户特征是上次登录日期距当天日期已经达到 7 天

select '2020-06-14' as dt,
    count(*) as user_lost_count
from user_login_log where dt = '2020-06-14'
    and last_login_date = date_sub('2020-06-14', 7);

3.2 回流用户

统计当天（2020-06-14）的回流用户，此类用户的特征为当天（2020-06-14）登陆了平台，同时昨天的还属于流失用户，即前一天（2020-06-13）的最后登录日期在7天之前

• 取出 2020-06-14 登录的用户

select user_id from user_login_log 
where dt = '2020-06-14' 
and last_login_date = '2020-06-14';

• 取出 2020-06-13 还属于流失用户的用户

-- 此处 last_login_date <= date_sub('2020-06-14', 8) 
-- 表示前一天 date_sub('2020-06-14', 1) 处在流失状态的用户
select user_id from user_login_log
where dt = date_sub('2020-06-14', 1) 
and last_login_date <= date_sub('2020-06-14', 8);

• 将两份数据 join 起来获得流失用户数

with t1 as (
    select user_id from user_login_log 
    where dt = '2020-06-14' 
    and last_login_date = '2020-06-14'
),t2 as (
    select user_id from user_login_log
    where dt = date_sub('2020-06-14', 1) 
    and last_login_date <= date_sub('2020-06-14', 8)
) select '2020-06-14',count(distinct t1.user_id) 
from t1 inner join t2 on t1.user_id = t2.user_id;