抽空刷了牛客网SQL实战72题，最后几道以牛客网为例的题目还挺有挑战性，在此记录
统计时间段新用户次日留存率每日的次日留存率每日的新用户数每日新用户的次日留存求新登录用户次日留存
表login第1行表示id为2的用户在-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表里对应的数据为nullselect count(distinct b.user_id) from(select user_id,min(date) as date from login group by user_id) aleft join login bon 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 pfrom (select user_id,min(date) as date from login group by user_id)as a left join login bon b.user_id=a.user_idand b.date=date(a.date,'+1 day')
查询每个日期登录新用户数
查询每个日期登录新用户个数，并且查询结果按照日期升序排序，上面的例子查询结果如下:(输出0，可以用sqlite的ifnull函数尝试实现，select ifnull(null,1)的输出是1)
--Step1：筛选出所有日期,并升序排列select date from logingroup by dateorder by date--Step2：筛选出每个用户首次登录的时间select user_id,min(date) as date from logingroup by user_id--Step3:将两张表连接select * from(select date from loginGROUP BY dateorder by date) aleft join(select user_id,min(date) as date from logingroup by user_id) bon a.date=b.date--按照a.date分组，求出每组的人数select a.date,count(b.user_id) as new from(select date from loginGROUP BY dateorder by date) aleft join(select user_id,min(date) as date from logingroup by user_id) bon a.date=b.dategroup by a.date
结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序
--step1:筛选出每日新登录用户的user_idSELECT a.date,b.user_id as 登陆新用户 from (SELECT date from logingroup by dateorder by date) aleft join (SELECT user_id,min(date) as date from logingroup by user_id) bon a.date=b.date--step2:在每日新登录用户信息后追加一列第二天仍继续登录的user_id--------条件满足 首次登录日期+1 day=第二天登录日期left join login con DATE_ADD(b.date,INTERVAL 1 day)=c.dategroup by a.dateorder 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.000else round(count(DISTINCT c.user_id)*1.0/count(DISTINCT b.user_id),3) end as p from (SELECT date from logingroup by date) aleft join (SELECT user_id,min(date) as date from logingroup by user_id) bon a.date=b.dateleft join login con date(b.date,'+1 day')=c.dategroup by a.dateorder by a.date;
统计刷题用户信息
统计每个用户查询刷题信息，包括:用户的名字，以及用户用的设备名字，以及截止到某天，累计总共通过了多少题。查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的那一天的数据不需要输出（不存在没有登录却刷题的情况，但是存在登录了没刷题的情况，不会存在刷题表里面，有提交代码没有通过的情况，但是会记录在刷题表里，只不过通过数目是0）
就是考察表连接，同时注意累计求和SELECTc.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)FROMpassing_number as b left join user as c on b.user_id = c.idleft join login as a on b.user_id = a.user_id and b.date = a.dateleft join client as d on a.client_id = d.idorder by b.date,c.name
题目里的数据链接 提取码: 8jp8
。
如果觉得《sql里查询一个组和组的用户数怎么查?_【牛客网SQL刷题】留存率怎么算？》对你有帮助，请点赞、收藏，并留下你的观点哦！
