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

with lastest_rate_date as (
    select
    user_id,
    max(date(review_date)) as latest_review_date
    from
    content_reviews
    group by user_id
),
average_rate as (
    select
    user_id,
    round(avg(rating),2) as average_rating
    from
    content_reviews
    where course_id='DS-102'
    group by user_id
),
user_number as (
    select distinct
    user_id
    from
    course_completions
    where course_id='DS-102'
    and date_format(completion_date,'%m')='03'
)

select 
u.user_id,
average_rating,
case when average_rating>=4 then '优质反馈学员' else '普通反馈学员' end
as feedback_type,
latest_review_date
from
user_number u
inner join
average_rate a on u.user_id=a.user_id
inner join
lastest_rate_date r on u.user_id=r.user_id
order by feedback_type desc,average_rating desc,user_id;