hive学习（五）——面试题（活跃用户及红包用户）

前言：

本次文章主要以hivesql进行查询，涉及同一场景但较为复杂。
来源于：面试题八

前期准备：

image.png

1.建表导入数据

2.1用户活跃模型表

create table tmp_liujg_dau_based(
imp_date string comment '日期',
qimei  string comment '用户唯一标识',
is_new  string  comment '新用户表示，1表示新用户，0表示老用户',
;
ALTER TABLE tmp_liujg_dau_based COMMENT '用户活跃模型表';

2.2红包参与领取模型表

create table tmp_liujg_packed_based  
(
imp_date  string comment '日期',
report_time   string  comment '领取时间戳',
qimei  string  not null comment '用户唯一标识',
add_money string not null comment '领取金额，单位为分');
ALTER TABLE tmp_liujg_packed_based COMMENT '红包参与领取模型表';

2.3用户活跃模型表数据导入

insert into tmp_liujg_dau_based values('20190301','001','0')
;insert into tmp_liujg_dau_based values('20190301','002','0')
;insert into tmp_liujg_dau_based values('20190301','003','1')
;insert into tmp_liujg_dau_based values('20190301','004','1')
;insert into tmp_liujg_dau_based values('20190301','005','1')
;insert into tmp_liujg_dau_based values('20190301','006','1')
;insert into tmp_liujg_dau_based values('20190302','001','0')
;insert into tmp_liujg_dau_based values('20190302','002','0')
;insert into tmp_liujg_dau_based values('20190302','003','0')
;insert into tmp_liujg_dau_based values('20190302','005','0')
;insert into tmp_liujg_dau_based values('20190302','006','0')
;insert into tmp_liujg_dau_based values('20190302','007','1')
;insert into tmp_liujg_dau_based values('20190303','005','0')
;insert into tmp_liujg_dau_based values('20190303','006','0')
;insert into tmp_liujg_dau_based values('20190303','007','0')
;insert into tmp_liujg_dau_based values('20190303','008','1')
;insert into tmp_liujg_dau_based values('20190303','009','1')
;insert into tmp_liujg_dau_based values('20190303','010','1')
;insert into tmp_liujg_dau_based values('20190401','008','0')
;insert into tmp_liujg_dau_based values('20190401','009','0')
;insert into tmp_liujg_dau_based values('20190401','010','0')
;insert into tmp_liujg_dau_based values('20190401','011','1')
;insert into tmp_liujg_dau_based values('20190401','012','1')
;insert into tmp_liujg_dau_based values('20190402','009','0')
;insert into tmp_liujg_dau_based values('20190402','010','0')
;insert into tmp_liujg_dau_based values('20190402','011','0')
;insert into tmp_liujg_dau_based values('20190402','012','0')
;insert into tmp_liujg_dau_based values('20190402','013','1')
;insert into tmp_liujg_dau_based values('20190402','014','1')
;insert into tmp_liujg_dau_based values('20190501','001','0')
;insert into tmp_liujg_dau_based values('20190501','002','0')
;insert into tmp_liujg_dau_based values('20190501','008','0')
;insert into tmp_liujg_dau_based values('20190501','007','0')
;insert into tmp_liujg_dau_based values('20190501','015','1')
;insert into tmp_liujg_dau_based values('20190501','016','1')
;insert into tmp_liujg_dau_based values('20190501','017','1')
;insert into tmp_liujg_dau_based values('20190501','018','1')
;insert into tmp_liujg_dau_based values('20190601','008','0')
;insert into tmp_liujg_dau_based values('20190601','017','0')
;insert into tmp_liujg_dau_based values('20190601','018','0')
;insert into tmp_liujg_dau_based values('20190601','019','1')
;insert into tmp_liujg_dau_based values('20190601','020','1')
;insert into tmp_liujg_dau_based values('20190601','021','1')
;insert into tmp_liujg_dau_based values('20190601','022','1')
;insert into tmp_liujg_dau_based values('20190603','021','0')
;insert into tmp_liujg_dau_based values('20190603','022','0')
;insert into tmp_liujg_dau_based values('20190603','011','0')
;insert into tmp_liujg_dau_based values('20190603','012','0')
;insert into tmp_liujg_dau_based values('20190603','023','1')
;insert into tmp_liujg_dau_based values('20190701','023','0')
;insert into tmp_liujg_dau_based values('20190701','008','0')
;insert into tmp_liujg_dau_based values('20190701','011','0')
;insert into tmp_liujg_dau_based values('20190701','022','0')
;insert into tmp_liujg_dau_based values('20190701','012','0')
;insert into tmp_liujg_dau_based values('20190701','024','1')
;insert into tmp_liujg_dau_based values('20190701','025','1')
;insert into tmp_liujg_dau_based values('20190701','026','1')
;insert into tmp_liujg_dau_based values('20190701','027','1')
;insert into tmp_liujg_dau_based values('20190705','026','0')
;insert into tmp_liujg_dau_based values('20190705','027','0')
;insert into tmp_liujg_dau_based values('20190705','009','0')
;insert into tmp_liujg_dau_based values('20190705','010','0')
;insert into tmp_liujg_dau_based values('20190705','028','1')
;insert into tmp_liujg_dau_based values('20190705','029','1')
;

2.2红包参与领取模型表数据导入

insert into tmp_liujg_packed_based values('20190301','2019/03/01 10:15:01','001','1.05')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 13:15:01','001','2.30')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 11:15:01','002','0.80')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 17:15:01','002','0.89')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 14:15:01','003','2.12')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 18:15:01','003','1.12')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 17:15:01','005','1.12')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 19:15:01','005','0.12')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 09:15:01','006','0.98')
;insert into tmp_liujg_packed_based values('20190301','2019/03/01 11:15:01','006','1.45')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 09:30:01','001','0.78')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 19:30:01','001','0.88')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 10:30:01','003','0.68')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 09:30:01','005','1.01')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 14:30:01','005','1.88')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 14:30:01','006','1.88')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 10:30:01','006','0.68')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 15:30:01','007','0.68')
;insert into tmp_liujg_packed_based values('20190302','2019/03/02 16:30:01','007','1.78')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 16:30:01','005','0.68')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 08:50:01','006','0.32')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 10:50:01','006','1.78')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 10:50:01','007','0.32')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 15:50:01','008','1.01')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 17:50:01','008','1.68')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 10:30:01','010','1.88')
;insert into tmp_liujg_packed_based values('20190303','2019/03/03 15:50:01','010','0.32')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 09:50:00','008','0.18')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 11:50:00','009','0.88')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 12:35:00','009','0.32')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 17:50:00','010','1.01')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 12:35:00','011','1.68')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 12:50:00','011','0.88')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 15:50:00','012','0.32')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 17:50:00','012','1.68')
;insert into tmp_liujg_packed_based values('20190401','2019/04/01 12:35:00','012','1.88')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 09:50:00','009','0.18')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 19:50:00','009','1.18')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 17:50:00','010','0.88')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 16:50:00','010','0.32')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 19:50:00','010','0.32')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 16:50:00','013','0.88')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 19:55:00','013','0.88')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 20:55:00','013','1.01')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 19:55:00','014','0.32')
;insert into tmp_liujg_packed_based values('20190402','2019/04/02 20:55:00','014','1.01')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 09:50:00','001','1.18')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 09:55:00','002','0.32')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 10:55:00','002','0.32')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 10:00:00','007','0.88')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 10:55:00','015','0.88')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 10:00:00','015','0.32')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 14:00:00','017','1.01')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 15:00:00','017','1.01')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 15:30:00','018','0.88')
;insert into tmp_liujg_packed_based values('20190501','2019/05/01 16:30:00','018','0.68')
;insert into tmp_liujg_packed_based values('20190601','2019/06/01 09:50:00','008','1.38')
;insert into tmp_liujg_packed_based values('20190601','2019/06/01 14:50:00','017','0.88')
;insert into tmp_liujg_packed_based values('20190601','2019/06/01 11:50:00','019','1.01')
;insert into tmp_liujg_packed_based values('20190601','2019/06/01 13:50:00','019','0.88')
;insert into tmp_liujg_packed_based values('20190601','2019/06/01 14:50:00','019','0.68')
;insert into tmp_liujg_packed_based values('20190602','2019/06/02 09:50:00','021','0.38')
;insert into tmp_liujg_packed_based values('20190602','2019/06/02 13:50:00','012','0.88')
;insert into tmp_liujg_packed_based values('20190602','2019/06/02 15:50:00','012','1.12')
;insert into tmp_liujg_packed_based values('20190602','2019/06/02 13:59:00','023','0.88')
;insert into tmp_liujg_packed_based values('20190602','2019/06/02 15:50:00','023','1.01')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 09:50:00','023','0.38')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 13:50:00','023','0.78')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 14:50:00','008','0.68')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 14:50:00','024','0.68')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 15:50:00','024','1.68')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 14:50:00','026','0.68')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 18:50:00','026','1.68')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 14:50:00','027','0.68')
;insert into tmp_liujg_packed_based values('20190701','2019/07/01 19:35:00','027','1.11')
;insert into tmp_liujg_packed_based values('20190702','2019/07/02 09:50:00','026','0.78')
;insert into tmp_liujg_packed_based values('20190702','2019/07/02 11:50:00','026','0.78')
;insert into tmp_liujg_packed_based values('20190702','2019/07/02 13:50:00','028','1.01')
;insert into tmp_liujg_packed_based values('20190702','2019/07/02 14:35:00','028','0.88')
;insert into tmp_liujg_packed_based values('20190702','2019/07/02 15:35:00','028','0.33

解题过程：

1.计算2019年3月，每个月领过红包用户和未领红包用户的数量，平均月活跃天数（即本月平均活跃多少天）

解法一：

Select a.month,packed_user,total-packed_user as no_packed_user,day from ((
Select substring(imp_date,0,6) as month,count(distinct qimei) total,count(distinct imp_date) as day from tmp_liujg_dau_based group by substring(imp_date,0,6) having month>="201903") a
Left join 
(Select substring(imp_date,0,6) as month ,count(distinct qimei) packed_user from tmp_liujg_packed_based group by substring(imp_date,0,6) having month>="201903") b on a.month = b.month);

image.png

解法二：

With tmp as (
Select substring(a.imp_date,0,6) as month,a.qimei as total_qimei,b.qimei as packed_qimei,a.imp_date day,(case when b.qimei is not null then "hb" else "no_hb" end) packed_user from tmp_liujg_dau_based a left join tmp_liujg_packed_based b on
Substring(a.imp_date,0,6)=Substring(b.imp_date,0,6) and a.qimei=b.qimei)
Select month,packed_user,count(distinct total_qimei) ,count(distinct day) as day from tmp group by month,packed_user;

image.png

2.计算2019年3月至今，每个月活跃用户的注册日期，2019年3月1日前注册的用户日期填空即可

With tmp as(
Select min(imp_date) as new_date,qimei from tmp_liujg_dau_based where is_new="1" and imp_date>="20190301"  group by qimei )
Select substring(imp_date,0,6) month,a.qimei,new_date from tmp_liujg_dau_based a left join tmp b on a.qimei=b.qimei group by substring(imp_date,0,6),a.qimei,new_date having substring(imp_date,0,6)>="201903";

image.png

3.计算2019年3月至今，每日的用户次日留存率，领取红包用户的次日留存，未领取红包用户的次日留存率

每日用户次日留存率

Select imp_date,qimei,LEAD(imp_date,1) OVER(partition by qimei order by imp_date) as second_imp_date,(case when LEAD(imp_date,1) OVER(partition by qimei order by imp_date)-imp_date=1 then 1 else null end ) liucun from tmp_liujg_dau_based where imp_date>="20190301" group by qimei,imp_date;

image.png

With tmp as (Select imp_date,qimei,LEAD(imp_date,1) OVER(partition by qimei order by imp_date) as second_imp_date,(case when LEAD(imp_date,1) OVER(partition by qimei order by imp_date)-imp_date=1 then 1 else null end ) liucun from tmp_liujg_dau_based where imp_date>="20190301" group by qimei,imp_date)
Select imp_date,sum(liucun)/count(qimei) from tmp group by imp_date;

image.png

领取红包用户的次日留存

With tmp as (Select imp_date,qimei,LEAD(imp_date,1) OVER(partition by qimei order by imp_date) as second_imp_date,(case when LEAD(imp_date,1) OVER(partition by qimei order by imp_date)-imp_date=1 then 1 else null end ) liucun from tmp_liujg_packed_based where imp_date>="20190301" group by qimei,imp_date)
Select imp_date,sum(liucun)/count(qimei) from tmp group by imp_date;

image.png

连接查询（存在问题活跃用户表中的用户不一定存在于红包用户中）

select a.imp_date,
a.qimei,
b.qimei as hb_qimei,
(case when lead(imp_date,1) OVER(PARTITION BY a.qimei ORDER BY imp_date)-imp_date=1 then 1 else null end) as total_rank_times,
(case when lead(imp_date,1) OVER(PARTITION BY b.qimei ORDER BY imp_date)-imp_date=1 then 1 else null end)  as hb_rank_times,
(case when b.qimei is null and lead(imp_date,1) OVER(PARTITION BY a.qimei ORDER BY imp_date)-imp_date=1 then 1 else null end)  as no_hb_rank_times 
from tmp_liujg_dau_based a left join tmp_liujg_packed_based b on a.imp_date=b.imp_date and a.qimei=b.qimei where a.imp_date>="20190301" group by a.imp_date,a.qimei,b.qimei ;

image.png

With tmp as (
select a.imp_date,
a.qimei,
b.qimei as hb_qimei,
(case when lead(imp_date,1) OVER(PARTITION BY a.qimei ORDER BY imp_date)-imp_date=1 then 1 else null end) as total_rank_times,
(case when lead(imp_date,1) OVER(PARTITION BY b.qimei ORDER BY imp_date)-imp_date=1 then 1 else null end)  as hb_rank_times,
(case when b.qimei is null and lead(imp_date,1) OVER(PARTITION BY a.qimei ORDER BY imp_date)-imp_date=1 then 1 else null end)  as no_hb_rank_times 
from tmp_liujg_dau_based a left join tmp_liujg_packed_based b on a.imp_date=b.imp_date and a.qimei=b.qimei where a.imp_date>="20190301" group by a.imp_date,a.qimei,b.qimei )
Select imp_date,count(hb_qimei)/count(qimei) as per,
sum(total_rank_times)/count(qimei) as total_liucun,
sum(hb_rank_times)/count(hb_qimei) as hb_liucun,
sum(no_hb_rank_times)/count(qimei) as no_hb_liucun
 from tmp group by imp_date;

image.png

4.计算2019年6月1日至今，每日新用户领取得第一个红包的金额

With tmp as (
Select  a.imp_date,report_time,a.qimei,add_money,ROW_Number() over (partition by a.imp_date,a.qimei order by report_time) rank  from tmp_liujg_packed_based a left join tmp_liujg_dau_based b on a.qimei=b.qimei and a.imp_date=b.imp_date where is_new="1")
Select imp_date,qimei,add_money from tmp where rank=1;

image.png

5.计算2019年3月1日至今，每个新用户领取的第一个红包和第二个红包的时间差（只计算注册当日有领取红包的用户，注册当日及以后的DAU表中新用户为1的用户）

Select a.imp_date,report_time,a.qimei,row_number() over(partition by a.imp_date,a.qimei order by report_time)  rank ,lead(a.report_time,1) over(partition by a.imp_date,a.qimei order by report_time) rank_time from tmp_liujg_packed_based a left join tmp_liujg_dau_based b on a.imp_date=b.imp_date and a.qimei=b.qimei where is_new="1")

image.png

由于无timestampdiff函数计算两者分钟差，只能进行自我换算后的加减

6.

image.png

Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei;

With tmp as (
Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei)
Select imp_date,count(qimei) from tmp where rank>=30 group by imp_date;

7.

image.png

#第一步：对每天登陆进行编号
Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei;

#第二步：利用date_sub(imp_date,rank)，查看是否一致（连续），然后进行分组 求（连续登陆天数）

Select qimei,imp_date,date_sub(imp_date,rank) as login_group,min(imp_date) as star_date1,max(imp_date) as end_date1,count(1) as continuous_days from
 (Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei) a
Group by qimei,date_sub(imp_date,rank);

#第三步：按qimei分组，取max(continuous_days)
Select qimei,star_date1,end_date1,max(continuous_days) from (
Select qimei,imp_date,date_sub(imp_date,rank) as login_group,min(imp_date) as star_date1,max(imp_date) as end_date1,count(1) as continuous_days from

 (Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei) a
)  b group by qimei;

8.

image.png


#第一步：对每天登陆进行编号
Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei;

#第二步：利用date_sub(imp_date,rank)，查看是否一致（连续），然后进行分组 求（连续登陆天数）
Select qimei,imp_date,date_sub(imp_date,rank) as login_group,min(imp_date) as star_date1,max(imp_date) as end_date1,count(1) as continuous_days from
 (Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei) a
Group by qimei,date_sub(imp_date,rank);

#第三步：求相邻时间差 第一条数据的end_date - 第二条数据的star_date
Select qimei,imp_date,date_sub(imp_date,rank) as login_group,min(imp_date) as star_date1,max(imp_date) as end_date1,count(1) as continuous_days,lead(min(imp_date),1,max(imp_date))-max(imp_date) as un_logintime from
 (Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei) a
Group by qimei,date_sub(imp_date,rank);

#第四步：分组取max(un_logintime)
Select qimei,imp_date,max(un_logintime) from 
(Select qimei,imp_date,date_sub(imp_date,rank) as login_group,min(imp_date) as star_date1,max(imp_date) as end_date1,count(1) as continuous_days,lead(min(imp_date),1,max(imp_date))-max(imp_date) as un_logintime from

 (Select qimei,imp_date,row_number() over(partition by qimei order by imp_date) rank from tmp_liujg_packed_based group by imp_date,qimei) a
Group by qimei,date_sub(imp_date,rank)b)c group by qimei;

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 216,163评论 6赞 498
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 92,301评论 3赞 392
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 162,089评论 0赞 352
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 58,093评论 1赞 292
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 67,110评论 6赞 388
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 51,079评论 1赞 295
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 40,005评论 3赞 417
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 38,840评论 0赞 273
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 45,278评论 1赞 310
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 37,497评论 2赞 332
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 39,667评论 1赞 348
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 35,394评论 5赞 343
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 40,980评论 3赞 325
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 31,628评论 0赞 21
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 32,796评论 1赞 268
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 47,649评论 2赞 368
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 44,548评论 2赞 352

hive学习（五）——面试题（活跃用户及红包用户）

前言：

前期准备：

1.建表导入数据

解题过程：

1.计算2019年3月，每个月领过红包用户和未领红包用户的数量，平均月活跃天数（即本月平均活跃多少天）

2.计算2019年3月至今，每个月活跃用户的注册日期，2019年3月1日前注册的用户日期填空即可

3.计算2019年3月至今，每日的用户次日留存率，领取红包用户的次日留存，未领取红包用户的次日留存率

4.计算2019年6月1日至今，每日新用户领取得第一个红包的金额

5.计算2019年3月1日至今，每个新用户领取的第一个红包和第二个红包的时间差（只计算注册当日有领取红包的用户，注册当日及以后的DAU表中新用户为1的用户）

6. image.png

7. image.png

8. image.png

推荐阅读更多精彩内容

6.

image.png

7.

image.png

8.

image.png