牛客SQL刷题总结（二）

1. 牛客每个人最近的登录日期问题A
   

   -- 方法一：
   SELECT user.name AS u_n, client.name AS c_n, login.date AS d
   FROM login 
   INNER JOIN user ON login.user_id = user.id
   INNER JOIN client ON login.client_id = client.id
   WHERE (login.user_id,login.date) IN (SELECT user_id, MAX(date) 
                                        FROM login 
                                        GROUP BY login.user_id )
   ORDER BY user.name;
   
   -- 方法二：窗口函数分组求最大时间，然后用子查询筛选
   SELECT new.u_n,new.c_n,new.d 
   FROM(SELECT u.name AS u_n, c.name AS c_n, l.date,
       (MAX(l.date) over(PARTITION BY l.user_id)) d
        FROM login l 
        INNER JOIN user u ON l.user_id=u.id
        INNER JOIN client c ON c.id=l.client_id) new
   WHERE new.date = new.d
   ORDER BY new.u_n;
   

2. 牛客每个人最近的登录日期问题B（求新用户的次日成功的留存率）
   

/*新登录用户的次日留存率*/
SELECT  
round(COUNT(DISTINCT user_id)*1.0/(SELECT COUNT(DISTINCT user_id) FROM login) ,3)
FROM login
WHERE (user_id, date)
IN (SELECT user_id, date(MIN(date),'+1 day') FROM login GROUP BY user_id);

-- 在MySQL中，查找某一天的后一天可以用date_sub(日期, interval -1 day)

3. 牛客每个人最近的登录日期问题C（求每个日期登录新用户个数）
   

   -- 方法一：
   SELECT login.date,ifnull(n1.new_num,0)
   FROM login 
   LEFT JOIN (SELECT l1.date,COUNT(DISTINCT l1.user_id) AS new_num
              FROM login l1
              WHERE l1.date = (SELECT MIN(date) FROM login WHERE user_id = l1.user_id)
              GROUP BY l1.date) n1
   ON login.date = n1.date
   GROUP BY login.date 
   ORDER BY login.date;
   
   -- 方法二：
   SELECT A.date,
   	   SUM(CASE WHEN rank=1 THEN 1 ELSE 0 END) AS new
   FROM (SELECT date, 
         ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date) rank
   	  FROM login) A
   GROUP BY A.date
   ORDER BY A.date;
   

4. 牛客每个人最近的登录日期问题D（求每个日期新用户的次日留存率）
   

   -- 注意理解为何使用LEFT JOIN
   SELECT a.date, ROUND(COUNT(b.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 b
   ON a.user_id = b.user_id
   AND b.date = date(a.date, '+1 day')
   GROUP BY a.date
   UNION
   SELECT date, 0.000 AS p
   FROM login
   WHERE date NOT IN (
       SELECT MIN(date)
       FROM login
       GROUP BY user_id)
   ORDER BY date;
   

5. 牛客每个人最近的登录日期问题E（累加问题）
   

   -- 方法一：用sum() over()求累加
   SELECT user.name AS u_n, client.name AS c_n, login.date, p1.ps_num
   FROM login 
   INNER JOIN (SELECT user_id, date ,
               sum(number) over(partition by user_id order by date) ps_num 
               FROM passing_number) p1
   ON p1.user_id = login.user_id AND p1.date = login.date
   INNER JOIN user 
   ON login.user_id = user.id
   INNER JOIN client 
   ON login.client_id = client.id
   ORDER BY login.date, user.name;
   
   -- 方法二：
   SELECT user.name AS u_n, client.name AS c_n, login.date, p1.ps_num
   FROM login 
   INNER JOIN (SELECT pn1.user_id,pn1.date,
               sum(pn2.number) as ps_num
               FROM passing_number pn1,passing_number pn2 
               WHERE pn1.user_id = pn2.user_id AND pn1.date >= pn2.date 
               GROUP BY pn1.user_id, pn1.date) p1
   ON p1.user_id = login.user_id AND p1.date=login.date
   INNER JOIN user 
   ON login.user_id = user.id
   INNER JOIN client 
   ON login.client_id=client.id
   ORDER BY login.date, user.name;
   

6. 考试分数问题A
   

   -- 使用窗口函数
   SELECT id, name, score 
   FROM (SELECT g.id, l.name, g.score,
           DENSE_RANK() OVER (PARTITION BY g.language_id ORDER BY g.score DESC) rnk
         FROM grade g 
         INNER JOIN language l 
         ON g.language_id = l.id) temp
   WHERE temp.rnk < 3
   ORDER BY name, score DESC, id;
   

7. 考试分数问题B
   

   SELECT job, 
       CAST(CASE WHEN total % 2 = 0 THEN total/2 ELSE (total + 1) / 2 END AS INTEGER) AS start,
       CAST(CASE WHEN total % 2 = 0 THEN total/2 + 1 ELSE (total + 1) / 2 END AS INTEGER) AS end
   FROM (SELECT job, COUNT() AS total 
         FROM grade 
         GROUP BY job)g
   ORDER BY job;
   

8. 考试分数问题C

   -- 方法一：
   SELECT a.*
   FROM
       (SELECT *, 
           DENSE_RANK() OVER(PARTITION BY job ORDER BY score DESC) rnk
       FROM grade)a
   INNER JOIN
       (SELECT job, 
           CAST(CASE WHEN total % 2 = 0 THEN total/2 ELSE (total + 1) / 2 END AS INTEGER) AS start,
           CAST(CASE WHEN total % 2 = 0 THEN total/2 + 1 ELSE (total + 1) / 2 END AS INTEGER) AS end
       FROM (SELECT job, COUNT() AS total 
             FROM grade 
             GROUP BY job)g)b
   ON a.job = b.job AND (a.rnk = b.start OR a.rnk = b.end)
   ORDER BY a.id;
   
   -- 方法二：
   SELECT id, job, score, dn AS rank
   FROM (SELECT *,
        rank() over(partition by job order by score)rn, -- 正序
        rank() over(partition by job order by score DESC)dn, -- 逆序
        COUNT() over(partition by job)cnt -- 参与排序总数
        FROM grade)
   WHERE (cnt % 2 = 1 AND rn = dn) -- 若排序总数为奇数，则中位数的正序位置与倒序位置相等
         OR (cnt % 2 = 0 AND ABS(rn - dn) = 1) -- 若排序总数为偶数，则中位数的正序位置与到虚为止正好相差1
   ORDER BY id;