with tiaojian as(
select 
user_id,
course_id,
completion_date
from course_completions
where
date_format(completion_date,'%Y%m')=202503
and 
course_id='DS-102'
)


select 
t.user_id,
t.average_rating,
case when t.average_rating>=4.0 then '优质反馈学员' else '普通反馈学员' end as feedback_type,
date(review_date) as latest_review_date
from(
select 
t.user_id,
round(
avg(case when t.course_id=ct.course_id then rating end)over(partition by t.user_id),2) as average_rating,
dense_rank()over(partition by t.user_id order by review_date desc) as py,
review_date
from tiaojian t inner join content_reviews ct 
on t.user_id=ct.user_id 
) as t 
where
t.py=1
order by feedback_type desc,average_rating desc,user_id