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

SQL77 牛客的课程订单分析(一)

SELECT * FROM order_info
WHERE `date` > '2025-10-15' AND `status` = 'completed' AND product_name IN ('C++', 'Java', 'Python')
ORDER BY id

SQL78 牛客的课程订单分析(二)

SELECT user_id
FROM order_info
WHERE `date` > '2025-10-15' AND `status` = 'completed' AND product_name IN ('C++', "Java", "Python")
GROUP BY user_id
HAVING COUNT(id) >= 2
ORDER BY user_id ASC

SQL79 牛客的课程订单分析(三)

SELECT temp.id, temp.user_id, temp.product_name, temp.status, temp.client_id, temp.date
FROM (
    SELECT *, COUNT(id) OVER (PARTITION BY user_id) AS buy_count
    FROM order_info
    WHERE `date` > '2025-10-15' AND `status` = 'completed' AND product_name IN ('C++', 'Java', 'Python')
) AS temp
WHERE temp.buy_count >= 2
ORDER BY temp.id

SQL80 牛客的课程订单分析(四)

SELECT temp.user_id, temp.date AS first_buy_date, temp.cnt
FROM (
    SELECT
        user_id,
        `date`,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY `date` ASC) AS date_ranking,
        COUNT(id) OVER (PARTITION BY user_id) AS cnt
    FROM order_info
    WHERE `date` > '2025-10-15' AND `status` = 'completed' AND product_name IN ('C++', 'Java', 'Python')
) temp
WHERE temp.cnt >= 2 AND temp.date_ranking = 1
ORDER BY temp.user_id ASC

SQL81 牛客的课程订单分析(五)

SELECT
    temp.user_id,
    MAX(IF(temp.buy_ranking = 1, `date`, NULL)) AS first_buy_date,
    MAX(IF(temp.buy_ranking = 2, `date`, NULL)) AS second_buy_date,
    COUNT(1) AS cnt
FROM (
    SELECT
        user_id,
        `date`,
        ROW_NUMBER() OVER (PARTITION BY `user_id` ORDER BY `date` ASC) AS buy_ranking,
        COUNT(id) OVER (PARTITION BY `user_id`) AS buy_count
    FROM order_info
    WHERE `date` > '2025-10-15' AND `status` = 'completed' AND product_name IN ('C++', 'Java', 'Python')
) temp
WHERE temp.buy_count >= 2
GROUP BY temp.user_id

SQL82 牛客的课程订单分析(六)

SELECT temp.id, temp.is_group_buy, temp.client_name
FROM (
    SELECT o.id, o.is_group_buy, c.name AS client_name, COUNT(o.id) OVER (PARTITION BY o.user_id) AS buy_count
    FROM order_info o LEFT JOIN client c ON o.client_id = c.id
    WHERE `date` > '2025-10-15' AND `status` = 'completed' AND product_name IN ('C++', 'Java', 'Python')
) temp
WHERE temp.buy_count >= 2
ORDER BY temp.id ASC

SQL83 牛客的课程订单分析(七)

SELECT IF(c.name IS NULL, 'GroupBuy', c.name) AS source, COUNT(*) AS cnt
FROM (
    SELECT
        client_id,
        COUNT(id) OVER (PARTITION BY `user_id`) AS buy_count
    FROM order_info
    WHERE `date` > '2025-10-15' AND `status` = 'completed' AND product_name IN ('C++', 'Java', 'Python')
) temp LEFT JOIN client c ON temp.client_id = c.id
WHERE temp.buy_count >= 2
GROUP BY source
ORDER BY source

SQL84 实习广场投递简历分析(一)

SELECT job, SUM(num) AS cnt
FROM resume_info
WHERE YEAR(date) = '2025'
GROUP BY job
ORDER BY cnt DESC

SQL85 实习广场投递简历分析(二)

SELECT job, SUBSTR(date, 1, 7) AS mon, SUM(num) AS cnt
FROM resume_info
WHERE YEAR(date) = '2025'
GROUP BY mon, job
ORDER BY mon DESC, cnt DESC

SQL86 实习广场投递简历分析(三)

WITH resume_info_by_month AS (
    SELECT job, SUBSTR(`date`, 1, 4) AS `year`, SUBSTR(`date`, 6, 2) AS mon, SUM(num) AS cnt
    FROM resume_info
    WHERE YEAR(`date`) IN ('2025', '2026')
    GROUP BY job, `year`, mon
)
SELECT r1.job, CONCAT(r1.year, '-', r1.mon) AS first_year_mon, r1.cnt AS first_year_cnt, CONCAT(r2.year, '-', r2.mon) AS second_year_mon, r2.cnt AS second_year_cnt
FROM resume_info_by_month r1 INNER JOIN resume_info_by_month r2 ON r1.job = r2.job AND r1.mon = r2.mon
WHERE r1.year = '2025' AND r2.year = '2026'
ORDER BY first_year_mon DESC, r1.job DESC

SQL87 最差是第几名(一)

SELECT grade, SUM(number) OVER (ORDER BY grade ASC) AS t_rank
FROM class_grade

SQL88 最差是第几名(二)

WITH t AS (
    SELECT SUM(number) AS total FROM class_grade
)
SELECT temp.grade
FROM (
    SELECT grade, SUM(number) OVER (ORDER BY grade ASC) AS running_sum_asc, SUM(number) OVER (ORDER BY grade DESC) AS running_sum_desc
    FROM class_grade
) temp
WHERE temp.running_sum_asc >= FLOOR(((SELECT total FROM t) + 1) / 2) AND temp.running_sum_desc >= FLOOR(((SELECT total FROM t) + 1) / 2)
ORDER BY grade

SQL89 获得积分最多的人(一)

SELECT u.name, SUM(g.grade_num) AS grade_sum
FROM `user` u INNER JOIN grade_info g ON u.id = g.user_id
GROUP BY u.id
ORDER BY grade_sum DESC
LIMIT 1

SQL90 获得积分最多的人(二)

SELECT temp1.user_id, u.name, temp1.grade_sum
FROM (
    SELECT temp2.user_id, temp2.grade_sum, RANK() OVER (ORDER BY temp2.grade_sum DESC) AS ranking
    FROM (
        SELECT user_id, SUM(grade_num) AS grade_sum
        FROM grade_info
        GROUP BY user_id
    ) temp2
) temp1 INNER JOIN `user` u ON temp1.user_id = u.id
WHERE temp1.ranking = 1

SQL91 获得积分最多的人(三)

SELECT temp1.user_id, temp1.name, temp1.grade_sum
FROM (
    SELECT temp2.*, RANK() OVER (ORDER BY grade_sum DESC) AS ranking
    FROM (
        SELECT
            g.user_id,
            u.name,
            SUM(CASE g.type WHEN 'add' THEN g.grade_num ELSE -g.grade_num END) AS grade_sum
        FROM `user` u INNER JOIN grade_info g ON u.id = g.user_id
        GROUP BY g.user_id
    ) AS temp2
) AS temp1
WHERE temp1.ranking = 1