首页 > 试题广场 >

在线教育平台活跃学员课程评价分析

[编程题]在线教育平台活跃学员课程评价分析
  • 热度指数：212 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解

背景

在一个在线教育平台中，我们希望分析完成了高级课程的学员对其所学课程内容的评价情况，以识别出高参与度且提供高质量反馈的学员。为此，我们需要关联学员的课程完成记录和他们提交的内容评价。

表结构和字段说明

表1：课程完成记录表 (course_completions)

  • completion_id: (INT, 主键) 完成记录的唯一标识。
  • user_id: (INT) 学员的唯一标识。
  • course_id: (VARCHAR) 课程的唯一标识。
  • completion_date: (DATE) 课程完成日期。

表2：内容评价表 (content_reviews)

  • review_id: (INT, 主键) 评价的唯一标识。
  • user_id: (INT) 提交评价的学员ID。
  • course_id: (VARCHAR) 评价内容所属的课程ID。
  • rating: (INT) 评价星级，范围1-5。
  • review_date: (DATETIME) 评价提交的具体时间。

任务要求

请查询在2025年3月完成了 “数据科学进阶” (course_id= 'DS-102') 课程的所有学员。对于这些学员，请计算他们对该课程 (DS-102) 所有评价的平均星级，并找出他们最近一次提交评价的日期（不限课程）。同时，根据平均星级将学员分为两类：平均星级大于等于4.0的为 "优质反馈学员"，否则为 "普通反馈学员"。

查询结果要求

请返回以下字段：

  • 学员ID (user_id)
  • 平均星级 (average_rating)，结果四舍五入保留2位小数。
  • 反馈类型 (feedback_type)
  • 最近评价日期 (latest_review_date)，格式为 'YYYY-MM-DD'。

排序规则

查询结果请先按feedback_type升序排列（即 "普通反馈学员" 在前），然后按average_rating降序排列，最后按user_id升序排列。

示例1

输入

-- 创建课程完成记录表
CREATE TABLE course_completions (
    completion_id INT,
    user_id INT,
    course_id VARCHAR(10),
    completion_date DATE
);

-- 创建内容评价表
CREATE TABLE content_reviews (
    review_id INT,
    user_id INT,
    course_id VARCHAR(10),
    rating INT,
    review_date DATETIME
);

-- 插入示例数据
-- 课程完成记录
INSERT INTO course_completions (completion_id, user_id, course_id, completion_date) VALUES
(1, 101, 'DS-102', '2025-03-15'),
(2, 102, 'DS-102', '2025-03-20'),
(3, 103, 'PY-101', '2025-03-22'), -- 其他课程
(4, 104, 'DS-102', '2025-04-01'), -- 其他月份
(5, 101, 'PY-101', '2025-04-05'),
(6, 105, 'DS-102', '2025-03-28');

-- 内容评价
INSERT INTO content_reviews (review_id, user_id, course_id, rating, review_date) VALUES
(1, 101, 'DS-102', 5, '2025-03-10 10:00:00'),
(2, 101, 'DS-102', 4, '2025-03-14 11:00:00'),
(3, 102, 'DS-102', 3, '2025-03-18 14:00:00'),
(4, 102, 'DS-102', 4, '2025-03-21 15:00:00'),
(5, 101, 'PY-101', 5, '2025-04-10 09:00:00'), -- user_id=101 的最近评价
(6, 103, 'PY-101', 4, '2025-03-25 16:00:00'),
(7, 105, 'DS-102', 3, '2025-03-30 18:00:00'),
(8, 102, 'DS-102', 3, '2025-03-22 10:00:00');

输出

user_id|average_rating|feedback_type|latest_review_date
102|3.33|普通反馈学员|2025-03-22
105|3.00|普通反馈学员|2025-03-30
101|4.50|优质反馈学员|2025-04-10

说明

示例数据表
course_completions
completion_iduser_idcourse_idcompletion_date
1101DS-1022025-03-15
2102DS-1022025-03-20
3103PY-1012025-03-22
4104DS-1022025-04-01
5101PY-1012025-04-05
6105DS-1022025-03-28
content_reviews
review_iduser_idcourse_idratingreview_date
1101DS-10252025-03-10 10:00:00
2101DS-10242025-03-14 11:00:00
3102DS-10232025-03-18 14:00:00
4102DS-10242025-03-21 15:00:00
5101PY-10152025-04-10 09:00:00
6103PY-10142025-03-25 16:00:00
7105DS-10232025-03-30 18:00:00
8102DS-10232025-03-22 10:00:00
示例数据查询结果表
user_idaverage_ratingfeedback_typelatest_review_date
1023.33普通反馈学员2025-03-22
1053.00普通反馈学员2025-03-30
1014.50优质反馈学员2025-04-10
算法知识视频讲解
添加回答
牛客194200828号头像 牛客194200828号 
with temp1 as (
    select distinct user_id,round(avg(rating)over(partition by user_id),2) average_rating,
        case when round(avg(rating)over(partition by user_id),2) >=4 then '优质反馈学员' else '普通反馈学员' end feedback_type
    from content_reviews
    join course_completions using(user_id,course_id)
    where user_id in(
        select user_id
        from course_completions
        where review_date like '2025-03%'
        and course_id = 'DS-102'
    )
)
select user_id,average_rating,feedback_type,date(max(review_date))latest_review_date
from temp1
left join content_reviews
using (user_id)
group by user_id,average_rating,feedback_type
order by feedback_type desc,average_rating desc,user_id

发表于 2026-02-01 13:10:31 回复(0)
超级斯卡蒂头像 超级斯卡蒂
这题纯**，要求都说不明白，feedback得降序排列，然后review_date是每个人所有日期的最大值
发表于 今天 11:37:54 回复(0)
牛客733972631号头像 牛客733972631号 
-- 目标学员 = 在2025年3月 完成了 “数据科学进阶” (course_id= 'DS-102')]
with target_user as (
    select distinct 
        user_id 
    from
        course_completions  
    where    
        course_id = 'DS-102'   
        and completion_date < '2025-04-01'
),

-- 目标学员 对 课程 (DS-102) 的所有评价的平均星级  = 每个学员对该课程多次评分，求该学员对该课程的平均评分】
feedback_tb as (
    select
        user_id
        ,round(avg(rating), 2) as average_rating
    from 
        content_reviews    
    where 
        user_id in (select user_id from target_user)
        and course_id = 'DS-102'
    group by 
        user_id
)

select
    f.user_id
    ,f.average_rating
    ,case when f.average_rating >= 4.0 then '优质反馈学员' else '普通反馈学员' end as feedback_type
    ,m.latest_review_date

from 
    feedback_tb as f
    inner join (   -- 最近一次提交评价的日期（不限课程）
        select user_id, date_format(max(review_date), '%Y-%m-%d') as latest_review_date
        from content_reviews
        group by user_id  ) as m 
    on m.user_id = f.user_id
        
order by 
    case when feedback_type = '普通反馈学员' then 0 else 1 end asc ,
    f.average_rating desc ,
    f.user_id asc ;

发表于 2026-02-03 20:39:35 回复(1)
二喜头像 二喜 
with
    tmp1 as (
        select
            user_id,
            round(avg(rating), 2) as average_rating,
            case
                when round(avg(rating), 2) >= 4 then '优质反馈学员'
                else '普通反馈学员'
            end as feedback_type
            # ,date_format(max(review_date),'%Y-%m-%d') as latest_review_date
        from
            content_reviews
        where
            (user_id, course_id) in (
                select DISTINCT
                    user_id,
                    course_id
                from
                    course_completions
                where
                    date_format(completion_date, '%Y-%m') = '2025-03'
                    and course_id = 'DS-102'
            )
        group by
            user_id
    ),
    tmp2 as (
        select
            user_id,
            date_format(max(review_date), '%Y-%m-%d') as latest_review_date
        from
            content_reviews
        group by
            user_id
    )
select
    tmp1.user_id,
    tmp1.average_rating,
    tmp1.feedback_type,
    tmp2.latest_review_date
from
    tmp1
    join tmp2 on tmp1.user_id = tmp2.user_id
order by
    feedback_type desc,
    average_rating desc,
    tmp1.user_id asc
feedback如果不用case when的话，其实是desc
发表于 2026-02-01 21:08:30 回复(0)
Lovnly头像 Lovnly 
with stu as(
    select user_id
    from course_completions
    where course_id='DS-102'
),
avg as (
    select user_id,
           avg(rating) as average_rating
    from content_reviews
    where user_id in (select user_id from stu) and course_id='DS-102'
    group by user_id
)

select user_id,
       round(average_rating,2) as average_rating,
       case when average_rating>=4 then '优质反馈学员' else '普通反馈学员' end as feedback_type,
       date(dat) as latest_review_date
from
    (select distinct avg.user_id,average_rating,max(review_date) over(partition by user_id) as dat
    from content_reviews cr
    inner join avg on avg.user_id=cr.user_id
    where cr.user_id in (select user_id from stu)) new
order by field(feedback_type,'普通反馈学员','优质反馈学员'),average_rating desc ,user_id asc

发表于 2026-02-01 11:38:55 回复(0)
提交观点

问题信息

难度：
5条回答 21浏览

热门推荐

相关试题

在线教育平台活跃学员课程评价分析

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号