题解 | 统计用户获得积分

-- DATETIME类型数据可以直接相减，相减后返回一个以秒为单位的差值
-- 计算两个时间差值：TIMESTAMPDIFF(MINUTE, visit_time, leave_time) 以分为单位返回
-- 计算单次积分：FLOOR(TIMESTAMPDIFF(MINUTE, visit_time, leave_time)/10) AS single_point
-- 按照用户对积分进行累加：SUM(single_point) AS point GROUP BY user_id
-- 按照积分降序：ORDER BY point DESC

WITH T AS(
    SELECT 
        user_id,
        FLOOR(TIMESTAMPDIFF(MINUTE, visit_time, leave_time)/10) AS single_point
    FROM
        visit_tb
)
SELECT 
    user_id,
    SUM(single_point) AS point 
FROM T
GROUP BY user_id
ORDER BY point DESC