题解 | #牛客每个人最近的登录日期(三)#
牛客每个人最近的登录日期(三)
https://www.nowcoder.com/practice/16d41af206cd4066a06a3a0aa585ad3d
方法一：窗口函数
SELECT ROUND(SUM(IF(t2.rk=2,1,0))/SUM(IF(t2.rk=1,1,0)),3)p
FROM (
SELECT *,ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) as rk
FROM(
SELECT DISTINCT user_id,date
FROM login )t1
)t2
方法二：时间日期函数
SELECT ROUND( COUNT(l.date)/(SELECT COUNT(DISTINCT user_id) FROM login ) ,3)p
FROM login l
WHERE (l.user_id,l.date)IN(
SELECT user_id,DATE_ADD(MIN(date),INTERVAL 1 DAY) date
FROM login
GROUP BY user_id
)
投递烽火通信等公司10个岗位