抽空刷了牛客網(wǎng)SQL實戰(zhàn)72題，最后幾道以牛客網(wǎng)為例的題目還挺有挑戰(zhàn)性，在此記錄

• 統(tǒng)計時間段新用戶次日留存率
• 每日的次日留存率
• 每日的新用戶數(shù)
• 每日新用戶的次日留存

• 求新登錄用戶次日留存

表login第1行表示id為2的用戶在2020-10-12使用了客戶端id為1的設(shè)備第一次新登錄了牛客網(wǎng)。需寫出一個sql語句查詢新登錄用戶次日成功的留存率，即第1天登陸之后，第2天再次登陸的概率,保存小數(shù)點后面3位(3位之后的四舍五入)，例子查詢結(jié)果如下:

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

--Step1：篩選統(tǒng)計期內(nèi)，用戶首次登錄的信息（user_id以及date） select user_id,min(date) as date from login group by user_id--Step2：步驟一得到的表a和login表連接，計算出在登錄后的第二天又登錄的用戶數(shù) ----------- 滿足首次登錄日期+1day=第二天登錄日期 ----------- 如果不滿足，b表里對應(yīng)的數(shù)據(jù)為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:計算次日留存 次日登錄用戶數(shù)/登錄用戶數(shù) 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')

• 查詢每個日期登錄新用戶數(shù)

查詢每個日期登錄新用戶個數(shù)，并且查詢結(jié)果按照日期升序排序，上面的例子查詢結(jié)果如下:(輸出0，可以用sqlite的ifnull函數(shù)嘗試實現(xiàn)，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分組，求出每組的人數(shù) 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

思路示意圖

• 查詢每個日期新用戶的次日留存率

結(jié)果保留小數(shù)點后面3位數(shù)(3位之后的四舍五入)，并且查詢結(jié)果按照日期升序排序

--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:在每日新登錄用戶信息后追加一列第二天仍繼續(xù)登錄的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:計算次日留存率 --首日登錄人數(shù) count(distinct b.user_id) 次日登錄數(shù)count(distinct c.user_id) --將結(jié)果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 ;

• 統(tǒng)計刷題用戶信息

統(tǒng)計每個用戶查詢刷題信息，包括: 用戶的名字，以及用戶用的設(shè)備名字，以及截止到某天，累計總共通過了多少題。查詢結(jié)果先按照日期升序排序，再按照姓名升序排序，有登錄卻沒有刷題的那一天的數(shù)據(jù)不需要輸出（不存在沒有登錄卻刷題的情況，但是存在登錄了沒刷題的情況，不會存在刷題表里面，有提交代碼沒有通過的情況，但是會記錄在刷題表里，只不過通過數(shù)目是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

題目里的數(shù)據(jù)鏈接 提取碼: 8jp8

。

總結(jié)

以上是生活随笔為你收集整理的sql里查询一个组和组的用户数怎么查?_【牛客网SQL刷题】留存率怎么算？的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。