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

1. 注意with.. as ..的用法：with t1 as(...),t2 as(...) select...
2. 注意按出勤用户去重。

with t1 as(
  select
    course_id,
    count(distinct  if(
        timestampdiff(minute, in_datetime, out_datetime) >= 10,
        user_id,
        null
      ) ) as attendnum
  from
    attend_tb
  group by
    course_id
), t2 as(
  select
    course_id,
    course_name,
    sum(if_sign) as signnum
  from
    behavior_tb
    join course_tb using(course_id)
  group by
    course_id,
    course_name
)

select 
course_id,
course_name,
round(attendnum/signnum*100,2) as attendrate
from t2
join t1 using(course_id)
order by course_id