牛客网数据库SQL实战（三）

SQL61 对于employees表中，给出奇数行的first_name

SELECT temp.first_name
FROM (
    SELECT
        first_name, 
        ROW_NUMBER() OVER () AS row_no, 
        RANK() OVER (ORDER BY first_name ASC) AS ranking
    FROM employees
) temp
WHERE temp.ranking % 2 = 1
ORDER BY temp.row_no ASC

SQL62 出现三次以上相同积分的情况

SELECT number
FROM grade
GROUP BY number
HAVING COUNT(number) >= 3

SQL63 刷题通过的题目排名

SELECT id, number, DENSE_RANK() OVER (ORDER BY number DESC) AS t_rank
FROM passing_number
ORDER BY number DESC, id ASC

SQL64 找到每个人的任务

SELECT p.id, p.name, t.content
FROM person p LEFT JOIN task t ON p.id = t.person_id

SQL65 异常的邮件概率

SELECT `date`, ROUND(SUM(CASE type WHEN 'no_completed' THEN 1 ELSE 0 END) / COUNT(1), 3) AS p
FROM email
WHERE send_id IN (
    SELECT id FROM `user`
    WHERE is_blacklist = 0
) AND receive_id IN (
    SELECT id FROM `user`
    WHERE is_blacklist = 0
)
GROUP BY `date`
ORDER BY `date` ASC

SQL66 牛客每个人最近的登录日期(一)

SELECT user_id, MAX(`date`) AS d
FROM login
GROUP BY user_id
ORDER BY user_id

SQL67 牛客每个人最近的登录日期(二)

SELECT temp.u_n, temp.c_n, temp.date
FROM (
    SELECT u.name AS u_n, c.name AS c_n, l.date, RANK() OVER (PARTITION BY l.user_id ORDER BY l.date DESC) AS ranking
    FROM login l INNER JOIN `user` u ON l.user_id = u.id INNER JOIN client c ON l.client_id = c.id
) temp
WHERE temp.ranking = 1
ORDER BY u_n

SQL68 牛客每个人最近的登录日期(三)

SELECT ROUND(
    (SELECT COUNT(DISTINCT l.user_id)
    FROM login l
    WHERE l.user_id IN (
        SELECT l1.user_id
        FROM login l1
        WHERE l1.user_id = l.user_id AND DATEDIFF(l.date, l1.date) = 1
    ))
    /
    (SELECT COUNT(DISTINCT l.user_id)
    FROM login l), 
    3)
AS p

SQL69 牛客每个人最近的登录日期(四)

SELECT temp.date, SUM(CASE temp.ranking WHEN 1 THEN 1 ELSE 0 END) AS new
FROM (
    SELECT l.user_id, l.date, ROW_NUMBER() OVER (PARTITION BY l.user_id ORDER BY l.date) AS ranking
    FROM login l
) temp
GROUP BY temp.date
ORDER BY temp.date

SQL70 牛客每个人最近的登录日期(五)

WITH new_user AS (
    SELECT lr.user_id, lr.date, lr.ranking
    FROM (
        SELECT l.user_id, l.date, ROW_NUMBER() OVER (PARTITION BY l.user_id ORDER BY l.date) AS ranking
        FROM login l
    ) lr
    WHERE lr.ranking = 1
)
SELECT d.date, IFNULL(ROUND(ku.kept_cnt / nu.cnt, 3), 0) AS p
FROM (
    SELECT DISTINCT l.date
    FROM login l
) d LEFT JOIN (
    SELECT n.date, COUNT(DISTINCT n.user_id) AS kept_cnt
    FROM new_user n
    WHERE EXISTS (
        SELECT l1.user_id, l1.date
        FROM login l1
        WHERE n.user_id = l1.user_id AND DATEDIFF(l1.date, n.date) = 1
    )
    GROUP BY n.date
) ku ON d.date = ku.date LEFT JOIN (
    SELECT n.date, COUNT(DISTINCT n.user_id) AS cnt
    FROM new_user n
    GROUP BY n.date
) nu ON d.date = nu.date
ORDER BY d.date

SQL71 牛客每个人最近的登录日期(六)

SELECT u.name AS u_n, p.date, SUM(p.number) OVER (PARTITION BY p.user_id ORDER BY p.date) AS ps_num
FROM passing_number p INNER JOIN `user` u ON p.user_id = u.id
ORDER BY p.date, u_n

SQL72 考试分数(一)

SELECT job, ROUND(AVG(score), 3) AS `avg`
FROM grade
GROUP BY job
ORDER BY `avg` DESC

SQL73 考试分数(二)

SELECT temp.id, temp.job, temp.score
FROM (
    SELECT *, AVG(score) OVER (PARTITION BY job) AS avg_score
    FROM grade
) AS temp
WHERE temp.score > temp.avg_score

SQL74 考试分数(三)

SELECT temp.id, temp.name, temp.score
FROM (
    SELECT g.id, l.name, g.score, DENSE_RANK() OVER (PARTITION BY g.language_id ORDER BY g.score DESC) AS ranking
    FROM grade g INNER JOIN `language` l ON g.language_id = l.id
) temp
WHERE temp.ranking <= 2
ORDER BY temp.name ASC, temp.score DESC, temp.id ASC

SQL75 考试分数(四)

SELECT job, FLOOR((COUNT(*) + 1) / 2) AS `start`, CEILING((COUNT(*) + 1) / 2) AS `end`
FROM grade
GROUP BY job
ORDER BY job

SQL76 考试分数(五)

SELECT temp.id, temp.job, temp.score, temp.t_rank
FROM (
    SELECT g.id, g.job, g.score, ROW_NUMBER() OVER (PARTITION BY g.job ORDER BY g.score DESC) AS t_rank, COUNT(g.id) OVER (PARTITION BY g.job) AS t_count
    FROM grade g
) temp
WHERE temp.t_rank IN (FLOOR((temp.t_count + 1) / 2), CEILING((temp.t_count + 1) / 2))
ORDER BY temp.id