题解 | 推荐内容准确的用户平均评分

WITH t AS (
    SELECT SUM(
               CASE WHEN t1.rec_info_l=t2.hobby_l THEN 1
               ELSE 0
               END
    )  AS counts , AVG(t2.score) AS scores
    FROM recommend_tb t1
    LEFT JOIN user_action_tb t2 ON t1.rec_user=t2.user_id
    GROUP BY t1.rec_user
)
SELECT ROUND(AVG(scores),3) AS avg_score
FROM t
WHERE counts>=1