题解 | SQL 188. 牛客直播各科目出勤率

WITH t1 AS (
    SELECT
        course_id,
        COUNT(user_id) AS sign_count
    FROM behavior_tb
    WHERE if_sign = 1
    GROUP BY course_id
),
t2 AS (
    SELECT
        course_id,
        user_id,
        SUM(
            TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)
        )AS time_sum
    FROM attend_tb
    GROUP BY course_id, user_id
    HAVING time_sum >= 10
),
t3 AS (
    SELECT course_id,
           count(user_id) AS peo_num
    FROM t2
    GROUP BY course_id
),
t4 AS (
    SELECT a.course_id,
           a.course_name,
           ROUND(t3.peo_num / t1.sign_count * 100, 2) AS `attend_rate(%)`
    FROM course_tb a
    LEFT JOIN t1
    ON a.course_id = t1.course_id
    LEFT JOIN t3
    ON t1.course_id = t3.course_id
    ORDER BY a.course_id ASC
)
SELECT * FROM t4;

# 这道题的关键在于分步规划！