一点点积累

SQL题目汇总：
1.猿辅导：统计给定日期内每天都做新题的用户数，uesrid，qid，dt三个字段
限定日期，首先统计出每个用户做每道题的最初时间
然后再统计每个用户做题的天数有多少
然后筛选出做题天数等于这个给定期限天数的用户
然后统计用户数
select count(userid) as result
from(
select userid,count(distinct time) as day_times
from (
select userid,qid,min(dt) as time
form t
where dt between ' 2020-01-02' and '2020-06-30'
group by userid,qid)
group by userid
having count(distinct time)=datediff(day,'2020-01-02','2020-06-30'))

2、
执行顺序：from-on-join-where-group by-with-having-select-distinct-order by-limit

3、用SQL计算留存率
#用户行为信息表 userinfo
1.计算次日留存用户数
create table c as(select a.用户id,a.登陆时间 as a_t,b.登陆时间 as b_t
from 用户行为信息表 as a
left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机';)

select *,timestampdiff(day,a_t,b_t)
as day_interval
from c;

select a_t,count(distinct 用户id) as 活跃用户数,
count(distinct case when 时间间隔=1 then 用户id else null end) as 次日留存数,
count(distinct case when 时间间隔=1 then 用户id else null end)/ count(distinct 用户id) as 次日留存率,
count(distinct case when 时间间隔=3 then 用户id else null end) as 三日留存数,
count(distinct case when 时间间隔=3 then 用户id else null end)/ count(distinct 用户id) as 三日留存率,
count(distinct case when 时间间隔=7 then 用户id else null end) as 七日留存数,
count(distinct case when 时间间隔=7 then 用户id else null end)/ count(distinct 用户id) as 七日留存率
from
(select *,timestampdiff(day,a_t,b_t) as 时间间隔
from
(select a.用户id,a.登陆时间 as a_t,b.登陆时间 as b_t
from 用户行为信息表 as a
left join 用户行为信息表 as b
on a.用户id = b.用户id
where a.应用名称= '相机') as c
) as d
group by a_t;