刷題記錄 | 牛客網 SQL 程式設計題

1. 刷題通過的題目排名

select id, number, dense_rank()over(order by number desc) as rank
from passing_number
order by number desc, id asc;

注意：題目要求的排名是 1 2 2 3 稠密排名，所以用 dense_rank() 函數。

2. 出現三次以上積分相同的number

select number from grade
group by number
having count(number) >= 3;

3. 找到每個人的任務

select p.id, p.name, t.content
from person p
left join task t
on p.id = t.person_id
order by p.id asc;

題目要求person表中所有的id都要有，所以這個時候用 left join。

4. 每門課程考試前2名的學生

select id, name, score 
from
( select g.id, l.name, g.score, dense_rank() over( partition by language_id order by score desc) as ranking
   from grade g 
   join language l 
   on g.language_id = l.id
)
where ranking <= 2
order by  name,score desc, id;

思路：既然考到了名次，那麼首先想到的是用視窗函數中的排名函數。如果取前二名，直接用where判斷即可。
注意：

• 題目給出排序的條件有三個。注意寫法。
• 這裡的排名函數只有用 dense_rank() 的時候才給通過，其餘兩個都會報錯，大概是要考慮了分數相同的兩個人要屬於同一個名次，且不影響第二名。

5. 郵件異常的概率

要計算每天郵件異常的概率。條件是正常使用者發給正常使用者中成功的，所以要關聯表，提出掉黑名單裡的使用者。

select t.date, 
       round(sum( case when t.type='no_completed' then 1 else 0 end)*1.0 / count(*),3) as p 
from email t
where t.send_id in (
        select id 
        from user
        where is_blacklist = 0
        )
and t.receive_id in (
        select id 
        from user
        where is_blacklist = 0
        )
group by t.date
order by t.date asc;

解法一：是用where條件來篩出符合條件的使用者的。當然也可以用 inner join 來篩，方法如下：

select email.date, round(
    sum(case email.type when'completed' then 0 else 1 end)*1.0/count(email.type),3
) as p
from email
join user as u1 on (email.send_id=u1.id and u1.is_blacklist=0)
join user as u2 on (email.receive_id=u2.id and u2.is_blacklist=0)
group by email.date order by email.date;

6. 牛客網每個使用者最近一次登入的日期

用排名函數就可以解決。

select t.date
from 
	(
	select user_id, date, row_number() over( partition by user_id order by date desc) as rk
	from login
	) t
where t.rk = 1
order by t.user_id asc;

7. 牛客每個人最近一次登入 ②

相比於上一道題，這道題要求查詢出對應的使用者名稱和裝置名，其實也簡單，用兩個 join 連線就可以搞定。

select t.u_n, t.c_n, date as d 
from 
    (select l.user_id,  u.name as u_n, c.name as c_n, l.date, row_number() over(partition by l.user_id order by l.date desc) as rk
    from login l
    join user u 
    on l.user_id = u.id
    join client c 
    on l.client_id = c.id) t
where rk = 1
order by t.u_n asc;

8. 牛客新使用者的次日留存率

select round(count(login.user_id) * 1.0/count(a.user_id), 3) as p
from (
	select user_id, min(date) as date
	from login
	group by user_id ) a
left join login
on login.user_id = a.user_id
and login.date = date(a.date, '+1 day')

注：這是一道計算次日留存率的題目，也是面試和筆試考察的重點內容。有一定的難度，需要重點關注一下。

9. 統計每日新登入的使用者數

這道題的解法思路還挺巧的，對使用者進行分組並按照日期排名，取排名全為 1 的，即是首次登陸的使用者，然後再按照日期分組即可。

select a.date,
	   sum(case when rk = 1 then 1 else 0 end) new
from (
	select 
		user_id, 
		date, 
		row_number() over(partition by user_id order by date asc) as rk
	from login
	) a
group by date;

10. 計算每天新使用者的次日留存率

這道題的解法其實和第8道很像，但是唯一還需要考慮的是沒有新增使用者的日期。因此需要用到 union 函數。

select a.date, 
	   round(count(login.user_id) * 1.0 / count(a.user_id), 3) as p
from 
	(
	select user_id, min(date) as d
	from login
	group by user_id ) as a
left join login
on login.user_id = a.user_id
and login.date = date(a.date, '+1 day')
group by a.date   # 要看每天的留存，所以這一步不能少
union 
select date, 0.000 as p 
from login
where date not in (    # 沒有 is 直接 not in 
	select min(date) as date
	from login
	group by user_id)
order by date;

11. 統計累計刷題資訊

這道題需要注意是每天累計的通過題目的數量，用視窗函數 + 連線查詢可以實現。

select 
    u.name, 
    c.name, 
    pn.date, 
    sum(pn.number) over( partition by pn.user_id order by pn.date) as ps_num 
    # 按照使用者進行分類，看每一個使用者在date下的累計刷題數目。
from passing_number pn
left join user u 
on pn.user_id = u.id
left join login
on login.user_id = pn.user_id
and login.date = pn.date
left join client c 
on login.client_id = c.id
order by pn.date, u.name;

計算留存率終極程式碼

select *,
concat(round(100*次日留存使用者/日新增使用者數,2),'%')  次日留存率,
concat(round(100*三日留存使用者/日新增使用者數,2),'%')  三日留存率,
concat(round(100*七日留存使用者/日新增使用者數,2),'%')  七日留存率,
concat(round(100*三十日留存使用者/日新增使用者數,2),'%')  三十日留存率 
from 
(	
	select 
	c.log_day 日期,
	count(distinct c.u_id)  日新增使用者數,
	count(distinct d.u_id)  次日留存使用者,
	count(distinct e.u_id)  三日留存使用者,
	count(distinct f.u_id)  七日留存使用者,
	count(distinct g.u_id)  三十日留存使用者
	from 
	(
		-- 確保是新增使用者
		select a.*
		from user_login a 
		left join user_login b on a.u_id = b.u_id and b.log_day < a.log_day
		where b.log_day is null
	) c
	left join user_login d on c.u_id = d.u_id  and  DATEDIFF(d.log_day,c.log_day) = 1 
	left join user_login e on c.u_id = e.u_id  and  DATEDIFF(e.log_day,c.log_day) = 3
	left join user_login f on c.u_id = f.u_id  and  DATEDIFF(f.log_day,c.log_day) = 7
	left join user_login g on c.u_id = g.u_id  and  DATEDIFF(g.log_day,c.log_day) = 30
	group by c.log_day
) p;