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

WITH merged AS (
    SELECT
        b.user_id,
        c.course_name,
        b.course_id,
        b.if_vw,
        b.if_fav,
        b.if_sign,
        MAX(
            CASE
                WHEN TIMESTAMPDIFF(MINUTE, a.in_datetime, a.out_datetime) >= 10
                THEN 1
                ELSE 0
            END
        ) AS attend_10min_flag
    FROM behavior_tb b
    LEFT JOIN attend_tb a
        ON b.user_id = a.user_id
       AND b.course_id = a.course_id
    LEFT JOIN course_tb c
        ON b.course_id = c.course_id
    GROUP BY
        b.user_id,
        b.course_id,
        c.course_name,
        b.if_vw,
        b.if_fav,
        b.if_sign
)

SELECT
    course_id,
    course_name,
    ROUND(
        SUM(attend_10min_flag) * 100.0
        / COUNT(CASE WHEN if_sign = 1 THEN user_id END),
        2
    ) AS attend_rate
FROM merged
GROUP BY course_id, course_name
ORDER BY course_id;