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

WITH T1 AS (
    SELECT
    course_id,
    course_name,
    count(DISTINCT user_id) chuqinrenshu
    FROM
    attend_tb
    JOIN
    course_tb USING(course_id)
    WHERE 
    timestampdiff(minute,in_datetime,out_datetime) >= 10
    GROUP BY
    course_id,
    course_name
),
T2 AS (
    SELECT
    course_id,
    count(DISTINCT user_id) baomingrenshu
    FROM
    behavior_tb
    WHERE
    if_sign = 1
    GROUP BY 
    course_id
)
SELECT
    T1.course_id,
    T1.course_name,
    round((chuqinrenshu/baomingrenshu*100),2) attend_rate
FROM
    T1 JOIN T2 USING(course_id)
ORDER BY 
    T1.course_id ASC