sql 保留2位小数_【牛客网SQL刷题】留存率怎么算？-CSDN博客

抽空刷了牛客网SQL实战72题，最后几道以牛客网为例的题目还挺有挑战性，在此记录

统计时间段新用户次日留存率
每日的次日留存率
每日的新用户数
每日新用户的次日留存

求新登录用户次日留存

表login第1行表示id为2的用户在2020-10-12使用了客户端id为1的设备第一次新登录了牛客网。需写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)，例子查询结果如下:

(sqlite里查找某一天的后一天的用法是:date(yyyy-mm-dd, '+1 day')，四舍五入的函数为round，sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5)

--Step1：筛选统计期内，用户首次登录的信息（user_id以及date）
select user_id,min(date) as date from login 
group by user_id

--Step2：步骤一得到的表a和login表连接，计算出在登录后的第二天又登录的用户数
----------- 满足首次登录日期+1day=第二天登录日期
----------- 如果不满足，b表里对应的数据为null
select count(distinct b.user_id) from
(select user_id,min(date) as date from login 
group by user_id) a
left join login b
on a.user_id=b.user_id and  b.date=date(a.date,'+1 day')

--Step3:计算次日留存 次日登录用户数/登录用户数
select round(count(distinct b.user_id )*1.0/count(distinct a.user_id),3) as p
from 
(select user_id,min(date) as date from login 
group by user_id)as a 
left join login b
on b.user_id=a.user_id
and b.date=date(a.date,'+1 day')

查询每个日期登录新用户数

查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:(输出0，可以用sqlite的ifnull函数尝试实现，select ifnull(null,1)的输出是1)

--Step1：筛选出所有日期,并升序排列
select date from  login
group by date
order by date

--Step2：筛选出每个用户首次登录的时间
select user_id,min(date) as date from login
group by user_id

--Step3:将两张表连接
select * from
(select date from  login
GROUP BY date
order by date) a
left join
(select user_id,min(date) as date from login
group by user_id) b
on a.date=b.date

--按照a.date分组，求出每组的人数
select a.date,count(b.user_id) as new from
(select date from  login
GROUP BY date
order by date) a
left join
(select user_id,min(date) as date from login
group by user_id) b
on a.date=b.date
group by a.date

思路示意图

查询每个日期新用户的次日留存率

结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序

--step1:筛选出每日新登录用户的user_id
SELECT a.date,b.user_id as 登陆新用户 from 
(SELECT date from login
group by date
order by date) a
left join 
(SELECT user_id,min(date) as date from login
group by user_id) b
on a.date=b.date

--step2:在每日新登录用户信息后追加一列第二天仍继续登录的user_id
--------条件满足 首次登录日期+1 day=第二天登录日期
left join login c
on DATE_ADD(b.date,INTERVAL 1 day)=c.date
group by a.date
order by a.date

--step3:计算次日留存率
--首日登录人数 count(distinct b.user_id) 次日登录数count(distinct c.user_id)
--将结果null替换成0，可以用case when 解决
SELECT a.date ,
case when count(b.user_id)=0 then 0.000
else round(count(DISTINCT c.user_id)*1.0/count(DISTINCT b.user_id),3) 
end as p 
from 
(SELECT date from login
group by date) a
left join 
(SELECT user_id,min(date) as date from login
group by user_id) b
on a.date=b.date
left join login c
on date(b.date,'+1 day')=c.date
group by a.date
order by a.date
;

统计刷题用户信息

统计每个用户查询刷题信息，包括: 用户的名字，以及用户用的设备名字，以及截止到某天，累计总共通过了多少题。查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的那一天的数据不需要输出（不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0）

就是考察表连接，同时注意累计求和
SELECT
c.name as u_n,d.name as c_n,b.date as date,
sum(number) over (partition by b.user_id order by b.date)
FROM
passing_number as b 
left join user as c on b.user_id = c.id
left join login as a on b.user_id = a.user_id 
and b.date = a.date
left join client as d on a.client_id = d.id
order by 
b.date,c.name

题目里的数据链接提取码: 8jp8

。