牛客网SQL进阶挑战（二）

SQL27 每类试卷得分前3名

SELECT ranking.tag, ranking.uid, ranking.ranking
FROM (
    SELECT temp.tag, temp.uid, ROW_NUMBER() OVER (PARTITION BY temp.tag ORDER BY temp.max_score DESC, temp.min_score DESC, temp.uid DESC) AS ranking
    FROM (
        SELECT info.tag, record.uid, MAX(record.score) AS max_score, MIN(record.score) AS min_score
        FROM examination_info AS info JOIN exam_record AS record ON info.exam_id = record.exam_id
        GROUP BY info.tag, record.uid
    ) temp
) ranking
WHERE ranking.ranking <= 3
ORDER BY ranking.tag, ranking.ranking

SQL28 第二快/慢用时之差大于试卷时长一半的试卷

SELECT tmp.exam_id, tmp.duration, tmp.release_time
FROM (
    SELECT
        finish.exam_id,
        finish.duration,
        finish.release_time,
        NTH_VALUE(finish.finish_time, 2) OVER (PARTITION BY finish.exam_id ORDER BY finish.finish_time DESC) AS slow2,
        NTH_VALUE(finish.finish_time, 2) OVER (PARTITION BY finish.exam_id ORDER BY finish.finish_time) AS fast2
    FROM (
        SELECT exam_record.exam_id, 
               TIMESTAMPDIFF(SECOND, exam_record.start_time, exam_record.submit_time) AS finish_time,
               examination_info.duration,
               examination_info.release_time
        FROM exam_record JOIN examination_info ON exam_record.exam_id = examination_info.exam_id
        WHERE submit_time IS NOT NULL
    ) finish
) tmp
WHERE tmp.slow2 IS NOT NULL AND tmp.fast2 IS NOT NULL AND tmp.slow2 - tmp.fast2 > 60 * tmp.duration / 2
GROUP BY tmp.exam_id, tmp.duration, tmp.release_time
ORDER BY tmp.exam_id DESC

SQL29 连续两次作答试卷的最大时间窗

SELECT day_win.uid, day_win.days_window, ROUND(max_win.cnt / max_win.max_window * day_win.days_window, 2) AS avg_exam_cnt
FROM (
    SELECT win.uid, MAX(TIMESTAMPDIFF(DAY, prev_start_time, start_time)) + 1 AS days_window
    FROM (
        SELECT uid, DATE(start_time) AS start_time, LAG(DATE(start_time), 1) OVER (PARTITION BY uid ORDER BY start_time) AS prev_start_time
        FROM exam_record
        WHERE start_time IS NOT NULL AND YEAR(start_time) = '2021'
    ) win
    WHERE win.prev_start_time IS NOT NULL
    GROUP BY win.uid
    HAVING MAX(TIMESTAMPDIFF(DAY, prev_start_time, start_time)) > 0
) day_win JOIN (
    SELECT history.uid, TIMESTAMPDIFF(DAY, history.first_start_time, history.last_start_time) + 1 AS max_window, COUNT(1) AS cnt
    FROM (
        SELECT uid, 
               FIRST_VALUE(DATE(start_time)) OVER (PARTITION BY uid ORDER BY start_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_start_time, 
               LAST_VALUE(DATE(start_time)) OVER (PARTITION BY uid ORDER BY start_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_start_time
        FROM exam_record
        WHERE start_time IS NOT NULL AND YEAR(start_time) = '2021'
    ) history
    WHERE history.first_start_time < history.last_start_time
    GROUP BY history.uid, TIMESTAMPDIFF(DAY, history.first_start_time, history.last_start_time) + 1
) max_win ON day_win.uid = max_win.uid
ORDER BY day_win.days_window DESC, avg_exam_cnt DESC

SQL30 近三个月未完成试卷数为0的用户完成情况

SELECT uid, SUM(IF(submit_time IS NOT NULL, 1, 0)) AS exam_complete_cnt
FROM (
    SELECT uid, start_time, submit_time, DENSE_RANK() OVER (PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m') DESC) AS month_rank
    FROM exam_record
) tmp
WHERE tmp.month_rank <= 3
GROUP BY uid
HAVING COUNT(start_time) = SUM(IF(submit_time IS NOT NULL, 1, 0))
ORDER BY exam_complete_cnt DESC, uid DESC

SQL31 未完成率较高的50%用户近三个月答卷情况

WITH selected_usr AS (
    SELECT incomplete.uid
    FROM (
        SELECT stat.uid, PERCENT_RANK() OVER (ORDER BY stat.incomplete_rate DESC) AS ranking
        FROM (
            SELECT rec.uid, 
                   SUM(IF(rec.submit_time IS NULL, 1, 0)) AS incomplete_cnt,
                   COUNT(1) AS total_cnt,
                   SUM(IF(rec.submit_time IS NULL, 1, 0)) / COUNT(1) AS incomplete_rate
            FROM exam_record AS rec JOIN examination_info AS exam ON rec.exam_id = exam.exam_id
            WHERE exam.tag = 'SQL'
            GROUP BY rec.uid
        ) stat
    ) incomplete
    WHERE incomplete.ranking <= 0.5
)
SELECT tmp.uid, tmp.start_month, tmp.total_cnt, tmp.complete_cnt
FROM (
    SELECT summary.*, RANK() OVER (PARTITION BY summary.uid ORDER BY summary.start_month DESC) AS month_ranking
    FROM (
        SELECT usr.uid,
               DATE_FORMAT(rec.start_time, '%Y%m') AS start_month,
               COUNT(1) AS total_cnt,
               SUM(IF(rec.submit_time IS NOT NULL, 1, 0)) AS complete_cnt
        FROM user_info AS usr JOIN exam_record AS rec ON usr.uid = rec.uid JOIN selected_usr ON usr.uid = selected_usr.uid
        WHERE usr.level IN ('6', '7')
        GROUP BY usr.uid, DATE_FORMAT(rec.start_time, '%Y%m')
    ) summary
) tmp
WHERE tmp.month_ranking <= 3
ORDER BY tmp.uid, tmp.start_month

SQL32 试卷完成数同比2020年的增长率及排名变化

WITH summary AS (
    SELECT stat.*, RANK() OVER (PARTITION BY stat.start_year ORDER BY stat.exam_cnt DESC) AS exam_cnt_rank
    FROM (
        SELECT info.tag, YEAR(rec.start_time) AS start_year, COUNT(1) AS exam_cnt
        FROM examination_info AS info JOIN exam_record AS rec ON info.exam_id = rec.exam_id
        WHERE rec.submit_time IS NOT NULL AND MONTH(rec.start_time) <= 6
        GROUP BY info.tag, YEAR(rec.start_time)
    ) stat
)
SELECT s1.tag,
       s1.exam_cnt AS exam_cnt_20,
       s2.exam_cnt AS exam_cnt_21,
       CONCAT(ROUND((s2.exam_cnt - s1.exam_cnt) / s1.exam_cnt * 100, 1), '%') AS growth_rate,
       s1.exam_cnt_rank AS exam_cnt_rank_20,
       s2.exam_cnt_rank AS exam_cnt_rank_21,
       CAST(s2.exam_cnt_rank AS SIGNED) - CAST(s1.exam_cnt_rank AS SIGNED) AS rank_delta
FROM summary s1, summary s2
WHERE s1.tag = s2.tag AND s1.start_year = 2020 AND s2.start_year = 2021
ORDER BY growth_rate DESC, exam_cnt_rank_21 DESC

SQL33 对试卷得分做min-max归一化

SELECT
    temp.uid,
    temp.exam_id,
    ROUND(AVG(IF(temp.min_score = temp.max_score, temp.score, 100 * (temp.score - temp.min_score) / (temp.max_score - temp.min_score))), 0) AS avg_new_score
FROM (
    SELECT
        record.uid,
        record.exam_id,
        record.score,
        MIN(record.score) OVER (PARTITION BY record.exam_id) AS min_score,
        MAX(record.score) OVER (PARTITION BY record.exam_id) AS max_score
    FROM examination_info AS info JOIN exam_record AS record ON info.exam_id = record.exam_id
    WHERE info.difficulty = 'hard' AND record.score IS NOT NULL
) temp
GROUP BY temp.exam_id, temp.uid
ORDER BY temp.exam_id ASC, avg_new_score DESC

SQL34 每份试卷每月作答数和截止当月的作答总数

SELECT tmp.exam_id, tmp.start_month, tmp.month_cnt, SUM(tmp.month_cnt) OVER (PARTITION BY tmp.exam_id ORDER BY tmp.exam_id, tmp.start_month) AS cum_exam_cnt 
FROM (
    SELECT exam_id, DATE_FORMAT(start_time, '%Y%m') AS start_month, COUNT(1) AS month_cnt
    FROM exam_record
    GROUP BY exam_id, DATE_FORMAT(start_time, '%Y%m')
) tmp
ORDER BY tmp.exam_id, tmp.start_month

SQL35 每月及截止当月的答题情况

SELECT au.start_month, 
       au.mau,
       IFNULL(auv.month_add_uv, 0) AS month_add_uv,
       MAX(IFNULL(auv.month_add_uv, 0)) OVER (ORDER BY au.start_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS max_month_add_uv,
       SUM(IFNULL(auv.month_add_uv, 0)) OVER (ORDER BY au.start_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cum_sum_uv
FROM (
    SELECT DATE_FORMAT(start_time, '%Y%m') AS start_month, COUNT(DISTINCT uid) AS mau
    FROM exam_record
    GROUP BY start_month
) au LEFT JOIN (
    SELECT add_uv.start_month, COUNT(DISTINCT add_uv.uid) AS month_add_uv
    FROM (
        SELECT MIN(DATE_FORMAT(start_time, '%Y%m')) AS start_month, uid
        FROM exam_record
        GROUP BY uid
    ) add_uv
    GROUP BY add_uv.start_month
) auv ON au.start_month = auv.start_month
ORDER BY au.start_month

SQL36 统计有未完成状态的试卷的未完成数和未完成率

SELECT
    exam_id,
    SUM(IF(submit_time IS NULL, 1, 0)) AS incomplete_cnt,
    ROUND(SUM(IF(submit_time IS NULL, 1, 0)) / COUNT(1), 3) AS incomplete_rate
FROM exam_record
GROUP BY exam_id
HAVING incomplete_cnt > 0

SQL37 0级用户高难度试卷的平均用时和平均得分

SELECT usr.uid,
       ROUND(AVG(IFNULL(record.score, 0)), 0) AS avg_score,
       ROUND(AVG(IF(record.submit_time IS NULL, exam.duration, TIMESTAMPDIFF(MINUTE, record.start_time, record.submit_time))), 1) AS avg_time_took
FROM user_info AS usr JOIN exam_record AS record ON usr.uid = record.uid
     JOIN examination_info AS exam ON exam.exam_id = record.exam_id
WHERE usr.level = '0' AND exam.difficulty = 'hard'
GROUP BY usr.uid

SQL38 筛选限定昵称成就值活跃日期的用户

SELECT uid, nick_name, achievement
FROM user_info
WHERE nick_name LIKE '牛客%号' AND achievement BETWEEN 1200 AND 2500 AND uid IN (
    SELECT uid
    FROM exam_record
    WHERE DATE_FORMAT(start_time, '%Y%m') = '202109'
    UNION ALL
    SELECT uid
    FROM practice_record
    WHERE DATE_FORMAT(submit_time, '%Y%m') = '202109'
)

SQL39 筛选昵称规则和试卷规则的作答记录

SELECT rec.uid, rec.exam_id, ROUND(AVG(rec.score), 0) AS avg_score
FROM exam_record AS rec JOIN user_info AS usr ON rec.uid = usr.uid JOIN examination_info AS exam ON rec.exam_id = exam.exam_id
WHERE (usr.nick_name REGEXP '^牛客[0-9]+号$' OR usr.nick_name REGEXP '^[0-9]+$') AND exam.tag REGEXP '^(c|C)' AND rec.score IS NOT NULL
GROUP BY rec.uid, rec.exam_id
ORDER BY uid, avg_score

SQL40 根据指定记录是否存在输出不同情况

WITH stat AS (
    SELECT usr.uid, 
           usr.level, 
           SUM(IF(exam.start_time IS NOT NULL, 1, 0)) AS answer_cnt,
           SUM(IF(exam.start_time IS NOT NULL AND exam.score IS NULL, 1, 0)) AS incomplete_cnt, 
           ROUND(SUM(IF(exam.start_time IS NOT NULL AND exam.score IS NULL, 1, 0)) / COUNT(1), 3) AS incomplete_rate
    FROM user_info AS usr LEFT JOIN exam_record AS exam ON usr.uid = exam.uid
    GROUP BY usr.uid, usr.level
)
SELECT uid, incomplete_cnt, incomplete_rate
FROM stat
WHERE `level` = 0 AND EXISTS (
    SELECT 1
    FROM stat
    WHERE `level` = 0 AND incomplete_cnt > 2
)
UNION ALL
SELECT uid, incomplete_cnt, incomplete_rate
FROM stat
WHERE answer_cnt > 0 AND NOT EXISTS (
    SELECT 1
    FROM stat
    WHERE `level` = 0 AND incomplete_cnt > 2
)
ORDER BY incomplete_rate

SQL41 各用户等级的不同得分表现占比

SELECT tmp.level, tmp.score_grade, ROUND(tmp.cnt / SUM(tmp.cnt) OVER (PARTITION BY tmp.level), 3) AS ratio
FROM (
    SELECT stat.level, stat.score_grade, COUNT(1) AS cnt
    FROM (
        SELECT usr.level, 
               CASE WHEN exam.score >= 90 THEN '优'
                    WHEN exam.score >= 75 THEN '良'
                    WHEN exam.score >= 60 THEN '中'
                    ELSE '差'
               END AS score_grade
        FROM user_info AS usr JOIN exam_record AS exam ON usr.uid = exam.uid
        WHERE exam.score IS NOT NULL
    ) stat
    GROUP BY stat.level, stat.score_grade
) tmp
ORDER BY tmp.level DESC, ratio DESC

SQL42 注册时间最早的三个人

SELECT uid, nick_name, register_time
FROM user_info
ORDER BY register_time
LIMIT 3

SQL43 注册当天就完成了试卷的名单第三页

SELECT algo.uid, algo.level, algo.register_time, MAX(rec.score) AS max_score
FROM (
    SELECT usr.uid, usr.level, usr.register_time
    FROM exam_record AS record JOIN examination_info AS exam ON record.exam_id = exam.exam_id
         JOIN user_info AS usr ON record.uid = usr.uid
    WHERE usr.job = '算法' AND DATE(usr.register_time) = DATE(record.submit_time) AND exam.tag = '算法'
) algo JOIN exam_record AS rec ON algo.uid = rec.uid
GROUP BY algo.uid
ORDER BY max_score DESC
LIMIT 6, 3

SQL44 修复串列了的记录

SELECT
    exam_id,
    SUBSTRING_INDEX(tag, ',', 1) AS tag,
    SUBSTRING_INDEX(SUBSTRING_INDEX(tag, ',', 2), ',', -1) AS difficulty,
    SUBSTRING_INDEX(tag, ',', -1) AS duration
FROM
    examination_info
WHERE
    INSTR(tag, ',') <> 0

SQL45 对过长的昵称截取处理

SELECT
    uid,
    IF(CHAR_LENGTH(nick_name) > 13, CONCAT(SUBSTR(nick_name, 1, 10), '...'), nick_name) AS `nick_name(1)`
FROM
    user_info
WHERE
    CHAR_LENGTH(nick_name) > 10

SQL46 大小写混乱时的筛选统计

SELECT stat1.tag, stat2.answer_cnt
FROM (
    SELECT i.tag, COUNT(1) AS answer_cnt
    FROM examination_info i JOIN exam_record r ON i.exam_id = r.exam_id
    GROUP BY i.tag
) stat1 JOIN (
    SELECT i.tag, COUNT(1) AS answer_cnt
    FROM examination_info i JOIN exam_record r ON i.exam_id = r.exam_id
    GROUP BY i.tag
) stat2 ON UPPER(stat1.tag) = stat2.tag
WHERE stat1.answer_cnt < 3 AND stat1.tag <> stat2.tag