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

分步查询然后相除即可

select
  ct.course_id,
  ct.course_name,
  round(M.mn * 100 / N.sn, 2)
from
  course_tb ct
  left join (
    select
      bt.course_id,
      sum(if_sign) sn
    from
      behavior_tb bt
    group by
      bt.course_id
  ) N on ct.course_id = N.course_id
  left join (
    select
      distinct count(user_id) OVER(
        PARTITION BY course_id
        order by
          course_id
      ) mn,
      course_id
    from
      attend_tb
    group by
      course_id,
      user_id
    having
      sum(TIMESTAMPDIFF(MINUTE, in_datetime, out_datetime)) >= 10
  ) M on M.course_id = N.course_id
order by
  ct.course_id