题解 | SQL290 获得积分最多的人(三)

WITH t1 AS (
	-- 第一步，先将积分表进行数据增减量化
    SELECT
    	user_id,
    	CASE WHEN type = 'add' THEN grade_num
    		 WHEN type = 'reduce' THEN -grade_num
    	END AS grade_num
    FROM
    grade_info
),
t2 AS (
	-- 第二步，计算每个用户的分组积分之和
    SELECT
    	user_id,
    	SUM(grade_num) AS grade_num
    FROM t1
    GROUP BY user_id
),
t3 AS (
	-- 第三步，进行用户积分的排序
    SELECT
    	user_id,
    	grade_num,
    	RANK() OVER(ORDER BY grade_num DESC) AS ranking
    FROM t2
),
t4 AS (
	-- 第四步，进行筛选排名第一的数据
    SELECT
    	user_id,
    	grade_num
    FROM t3
    WHERE ranking = 1
),
t5 AS (
	-- 第五步，与维度表关联
    SELECT 
    	t4.user_id AS id,
    	u.name,
    	t4.grade_num
    FROM t4
    LEFT JOIN user u
    ON t4.user_id = u.id
    ORDER BY id ASC
)
SELECT * FROM t5;