SQL题目练习 - 红包题目（8题）无标准答案。。 - 《SQL 学习笔记》

1.用户活跃模型表

create table tmp_liujg_dau_based(
imp_date varchar(20) not null comment '日期',
qimei  varchar(20) not null comment '用户唯一标识',
is_new  varchar(10) comment '新用户表示，1表示新用户，0表示老用户',
primary key(imp_date,qimei));
ALTER TABLE tmp_liujg_dau_based COMMENT '用户活跃模型表';

2.红包参与领取模型表

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

3.题目：

1、计算2019年6月1日至今，每日DAU（活跃用户量，即有登陆的用户）
2、计算20190601至今，每日领取红包的新用户数，老用户数，及人均领取金额，人均领取次数
3、计算2019年3月，每个月按领红包取天数为1、2、3……30、31天区分，计算取每个月领取红包的用户数，人均领取金额，人均领取次数
4、计算2019年3月，每个月领过红包用户和未领红包用户的数量，平均月活跃天数（即本月平均活跃多少天）
5、计算2019年3月至今，每个月活跃用户的注册日期，2019年3月1日前注册的用户日期填空即可
6、计算2019年3月至今，每日的用户次日留存率，领取红包用户的次日留存，未领取红包用户的次日留存率
7、计算2019年6月1日至今，每日新用户领取得第一个红包的金额
8.计算2019年3月1日至今，每个新用户领取的第一个红包和第二个红包的时间差（只计算注册当日有领取红包的用户，注册当日及以后的DAU表中新用户为1的用户）

1、计算2019年6月1日至今，每日DAU（活跃用户量，即有登陆的用户）

 select imp_date,count(distinct user_id) as dau
 from dau
 where imp_date>='2019-06-01'
 group by imp_date

2、计算20190601至今，每日领取红包的新用户数，老用户数，及人均领取金额，人均领取次数

select h.imp_date, 
            count(distinct(case is_new when 1 then h.user_id else null end)) as '新用户数',
            count(distinct(case is_new when 0 then h.user_id else null end)) as '老用户数',
            sum(add_money)/count(distinct h.user_id) as '人均领取金额',
            count(*)/count(distinct h.user_id) as '人均领取次数'
from hongbao h 
join dau d 
on h.user_id=d.user_id and h.imp_date=d.imp_date
where imp_date >='2019-06-01'
group by h.imp_date
order by h.imp_date;

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

select user_id, min(imp_date)
from dau
where user_id in (
    select distinct user_id
    from dau
    where imp_date>='2019-03-01'
)
group by user_id;

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

select d1.imp_date,
count(distinct d2.user_id)/count(distinct d1.user_id),
count(distinct(case when h.user_id is not null then d2.user_id else null))/count(distinct(case when h.user_id is not null then d1.user_id else null)),
count(distinct(case when h.user_id is not null then d2.user_id else null))/count(distinct(case when h.user_id is not null then d1.user_id else null))
from dau d1 left join dau d2
on datediff(d2.imp_date,d1.imp_date)=1 and d1.user_id=d2.user_id
left join hongbao h 
on d1.user_id=h.user_id
group by d1.imp_date;

2)在1）的前提下加满足条件：领红包日期<=登陆日期，才算领取红包用户

select d1.imp_date,
count(distinct d2.user_id)/count(distinct d1.user_id),
count(distinct(case when min_hongbao_date<=d1.imp_date then d2.user_id else null))/count(distinct(case when min_hongbao_date<=d1.imp_date then d1.user_id else null)),
count(distinct(case when min_hongbao_date>d1.imp_date then d2.user_id else null))/count(distinct(case when min_hongbao_date>d1.imp_date then d1.user_id else null))
from dau d1 
left join dau d2 
on d1.user_id=d2.user_id and datediff(d2.imp_date,d1.imp_date)=1
left join (
    select user_id,min(imp_date)as min_hongbao_date
    from hongbao
    group by user_id
) h 
on d1.user_id=h.user_id
where year(d1.imp_date)=2019 and month(d1.imp_date)>=6
group by d1.imp_date;

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

select h.user_id,h.report_time,h.add_money
from (
    select *,row_number()over(partition by user_id order by report_time) as flag
    from hongbao
) as h
join dau d on d.user_id=h.user_id
where year(d1.imp_date)=2019 and month(d1.imp_date)>=6 and d.is_new=1 and h.flag=1;

8.计算2019年6月1日至今，注册的新用户领取的第一个红包和第二个红包的时间差
思路1:

select user_id,timestampdiff(second,min(report_time),max(report_time)) as interval
from (
    select *,row_number()over(partition by user_id order by report_time) as flag
    from hongbao
) as h
where user_id in (
  select user_id 
  from dau   
  where year(imp_date)=2019 and month(imp_date)>=6 
  and is_new=1
) 
and flag<=2
group by user_id
having min(report_time)<max(report_time)#筛选出至少领取2次的用户

思路2:lead（）得到时间差，但没有row number（）无法得到第一个红包时间