5. 牛客网数据（二）（SQL72~NC83）

SQL72 考试分数(一)
请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位 (3位之后四舍五入)

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

SQL73 考试分数(二)
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序
法1. 窗口函数
因为窗口函数一般用在 SELECT 中而不能直接用在 WHERE 中，又因为 SELECT 执行顺序在 WHERE 之后，所以如果要用 WHERE 对窗口函数进行筛选就必须再嵌套一层查询

SELECT id, job, score
FROM(
    SELECT
        *,
        AVG(score) OVER(PARTITION BY job) AS avg
    FROM grade
) AS grade_new
WHERE score > avg
ORDER BY id;

法2. 子查询
因为子查询可以直接用在 WHERE 中构造筛选条件，所以对这道题来说，子查询写起来还简便一点（不过执行效率会低很多（虽然实验中发现区别并不大））

SELECT *
FROM grade g
WHERE score > (SELECT AVG(score) 
               FROM grade
               WHERE job = g.job)
ORDER BY id;

SQL74 考试分数(三)
请你找出每个岗位分数排名前2的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序
法1. 窗口函数

SELECT g1.id,l.name,g1.score
FROM(
   SELECT *,
          DENSE_RANK() OVER(PARTITION BY language_id ORDER BY score DESC) AS r
   FROM grade g
) g1
JOIN language l
ON g1.language_id=l.id 
WHERE r<=2
ORDER BY l.name, g1.score DESC, g1.id

法2. 子查询

SELECT
    g.id,
    name,
    score
FROM grade g
JOIN language l
ON g.language_id = l.id
WHERE (
    -- 1. 中式排名 = 比他高的【不同的】分数的个数 + 1：
    SELECT COUNT(DISTINCT score) + 1
    FROM grade 
    WHERE language_id = g.language_id
    AND score > g.score
) <= 2  -- 2. 筛选排名前 2 名的
ORDER BY name, score DESC, id;

SQL75 考试分数(四)
请你写一个sql语句查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序

SELECT job,
       (CASE WHEN count(*)%2=0 THEN ROUND(count(*)/2)
  ELSE ROUND((count(*)+1)/2) END
 ) AS start,
       (CASE WHEN count(*)%2=0 THEN ROUND(count(*)/2+1)
  ELSE ROUND((count(*)+1)/2) END
 ) AS end
FROM grade
GROUP BY job
ORDER BY job

看了评论区，发现直接用ROUND()就可以解决。ROUND(x)：返回参数X的四舍五入的一个整数

select a.job, round(count(a.id)/2), round((count(a.id)+1)/2)
from grade a
group by a.job
order by job

SQL76 考试分数(五)
请你写一个sql语句查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序
要得到每个岗位位于中位数位置的一个或两个成绩，思路如下：
1.为了下一步的筛选，构造两个辅助列，分别计算岗位内排名（为了得到中位数，应该用 ROW_NUMBER() ）和岗位内候选人数
2.选取中位数：若岗位内候选人数为奇数，就选取排名中间的一位，若是偶数，就选取中间的两位

WITH grade_new AS(
SELECT
    id,
    job,
    score,
    # 1. 构造两个辅助列，分别计算岗位内排名和岗位内候选人数：
    ROW_NUMBER() OVER(PARTITION BY job ORDER BY score DESC) AS t_rank,
    -- 以找中位数为目的，这里用 ROW_NUMBER() 比用 RANK() 或 DENSE_RANK() 更合适
    -- 注：ROW_NUMBER() 好像很难用子查询替换
    COUNT(id) OVER(PARTITION BY job) AS ct
FROM grade
)

SELECT 
    id,
    job,
    score,
    t_rank
FROM grade_new

WHERE 
    /* 2. 筛选中位数：
    若岗位内候选人数为奇数，就选取排名中间的一位;
    若是偶数，就选取中间的两位 */
    CASE 
    WHEN ct % 2 = 1 THEN t_rank = ct DIV 2 + 1
    ELSE t_rank = ct / 2 OR t_rank = ct / 2 + 1
    END
ORDER BY id;

SQL77 牛客的课程订单分析(一)
请你写出一个sql语句查询在2025-10-15以后状态为购买成功的C++课程或者Java课程或者Python的订单，并且按照order_info的id升序排序

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

SQL78 牛客的课程订单分析(二)
写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序

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

SQL79 牛客的课程订单分析(三)
请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序
法1：窗口函数

select t1.id, t1.user_id,t1.product_name,t1.status,t1.client_id,t1.date
from
(
    select *,count(id) over(partition by user_id) as number
    from order_info
    where datediff(date,"2025-10-15")>0
      and status ="completed"
      and product_name in ("C++","Java","Python")
) t1
where t1.number >1
order by t1.id

法2：子查询

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

SQL80 牛客的课程订单分析(四)
请你写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序

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

注意，用 GROUP BY 分组后确实可以用窗口函数，但这时窗口函数是基于整个 GROUP BY分组之后的查询结果而不是基于每组组内的查询结果。比如，想求一共分为了多少个组：SELECT COUNT() OVER() FROM ………… GROUP BY …… LIMIT 1;，如果不用窗口函数，就要在外面再嵌套一层查询用 COUNT() 来求分为了多少组。
??SQL81 牛客的课程订单分析(五)
法一：窗口函数

select
 a.user_id,
 max(case when a.rank_no=1 then a.date else 0 end) as first_buy_date,
 max(case when a.rank_no=2 then a.date else 0 end) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date >= '2025-10-16'
      and status = 'completed'
      and product_name in('C++','Java','Python')
    ) a
where a.rank_no<=2 and a.cnt>=2
group by a.user_id,a.cnt
order by a.user_id ;

为什么CASE前加MIN没懂？

法二：子查询+limit

WITH order_new AS(
    SELECT *
    FROM order_info o
    WHERE date > '2025-10-15'
    AND product_name IN ('C++', 'Java', 'Python')
    AND status = 'completed'
)

SELECT 
    user_id, 
    MIN(date) AS first_buy_date,
    (SELECT date 
     FROM order_new
     WHERE user_id = o.user_id
     ORDER BY date
     LIMIT 1, 1) AS second_buy_date,
    COUNT(id) AS cnt
FROM order_new o
GROUP BY user_id
HAVING COUNT(id) >= 2
ORDER BY user_id;

NC82 牛客的课程订单分析(六)
法一：窗口函数

select t.id,t.is_group_buy,c.name
from
(select *, count(*) over(partition by user_id) as cnt
from order_info
where date>'2025-10-15'
and product_name in ('C++','Java','Python')
and status='completed') t
left join client c on t.client_id=c.id
where t.cnt>=2
order by t.id

法二：子查询

SELECT o.id, is_group_buy, 
       (CASE WHEN is_group_buy='YES' THEN 'None' ELSE c.name END) AS client_name
FROM order_info o
LEFT JOIN client c
ON o.client_id=c.id
WHERE user_id IN (
SELECT user_id
FROM order_info
WHERE date > '2025-10-15'
AND product_name IN ('C++', 'Java', 'Python')
AND status = 'completed'
GROUP BY user_id
HAVING COUNT(id) >= 2
)
AND date > '2025-10-15'
AND product_name IN ('C++', 'Java', 'Python')
AND status = 'completed'
ORDER BY o.id

NC83 牛客的课程订单分析(七)
法一：窗口函数

SELECT 
     (CASE WHEN is_group_buy='Yes' THEN 'GroupBuy' 
      ELSE c.name END) AS source,
      COUNT(*) cnt
FROM (
   SELECT *,
          COUNT(*) OVER(PARTITION BY user_id) ct
   FROM order_info 
    WHERE date > '2025-10-15'
    and product_name in ('C++', 'Python', 'Java')
    and status = 'completed'
) o
LEFT JOIN client c ON o.client_id=c.id
WHERE ct>=2
GROUP BY source
ORDER BY source

法二：子查询

SELECT 
     (CASE WHEN is_group_buy='Yes' THEN 'GroupBuy' 
      ELSE c.name END) AS source,
      COUNT(*)
FROM order_info o
LEFT JOIN client c
ON o.client_id=c.id
WHERE user_id IN (
SELECT user_id
FROM order_info
WHERE date > '2025-10-15'
AND product_name IN ('C++', 'Java', 'Python')
AND status = 'completed'
GROUP BY user_id
HAVING COUNT(id) >= 2
)
AND date > '2025-10-15'
AND product_name IN ('C++', 'Java', 'Python')
AND status = 'completed'
GROUP BY client_id
ORDER BY source