题解 | #牛客的课程订单分析(七)#

with s AS(SELECT
gi.user_id,
u.name,
case 
when gi.type ='add' then gi.grade_num
when gi.type ='reduce' then -gi.grade_num
end AS gn
FROM grade_info gi 
LEFT JOIN user u on u.id =gi.user_id)

SELECT
x.user_id,
x.name,
x.hh
from
(SELECT
s.user_id,
s.name,
sum(s.gn) as hh,
rank()over(ORDER BY sum(s.gn) desc) mm
FROM s 
GROUP by s.user_id,s.name
ORDER by s.user_id)x
WHERE x.mm =1 
ORDER BY x.user_id