id  |       id_course  |  lesson_title
    1  |          3       |      Урок 1
    2  |          3       |      Урок 2
    3  |          3       |      Урок 3
    4  |          4       |      Урок 4
    5  |          4       |      Урок 5

id  |       id_lesson  |             email      |    date
  43  |          1       |     123@gmail.com      |    2018-07-09 13:17:05
  44  |          2       |     123@gmail.com      |    2018-07-09 13:18:05
  45  |          3       |     123@gmail.com      |    2018-07-09 13:19:05

function total_score($id){
    global $connection;
   $query = "SELECT l.id_course, h.email, MAX(h.date) FROM lesson l LEFT JOIN history h ON h.id_lesson = l.id WHERE l.id_course= '$id' GROUP BY l.id_course, h.email HAVING SUM(h.id_lesson IS NULL) = 0";
    $res = mysqli_query($connection, $query);
    $total_score = array();
    while($row = mysqli_fetch_assoc($res)){
        $total_score[$row['email']] = $row;
    }
    return $total_score;
}

create table `lesson`
(
  `id` int,
  `id_course` int,
  `lesson_title` text
);

create table history
(
  `id` int,
  `id_lesson` int,
  `email` text,
  `date` date
);

Insert into `lesson` (`id`,`id_course`,`lesson_title`) values
(1, 3, 'Урок 1'),
(2, 3, 'Урок 2'),
(3, 3, 'Урок 3'),
(4, 4, 'Урок 4'),
(5, 4, 'Урок 5')
;

Insert into `history` (`id`,`id_lesson`,`email`,`date`) values
(43, 1, '100@gmail.com', '2018-07-10'),
(44, 2, '100@gmail.com', '2018-07-11'),
(45, 3, '100@gmail.com', '2018-07-12'),
(46, 4, '110@gmail.com', '2018-07-13'),
(47, 5, '110@gmail.com', '2018-07-14'),
(48, 1, '200@gmail.com', '2018-07-15'),
(49, 2, '200@gmail.com', '2018-07-16'),
(50, 1, '300@gmail.com', '2018-07-17'),
(51, 4, '300@gmail.com', '2018-07-18')
;

select * from `lesson`;

select * from `history`;

select * from `history`;

select distinct
  h.`email`,
  l.`id_course`
from `lesson` l
 join `history` h on l.`id` = h.`id_lesson`

select distinct
  h.`email`,
  l.`id_course`
from `lesson` l
 join `history` h on l.`id` = h.`id_lesson`

select
  cr.`email`,
  cr.`id_course`,
  L0.`id` as id_lesson
from
  (
    select distinct
      h.`email`,
      L.`id_course`
    from `lesson` L
      join `history` h on L.`id` = h.`id_lesson`
  ) as cr
  join `lesson` L0 on cr.`id_course` = L0.`id_course`
order by
  cr.`email` asc,
  cr.`id_course` asc,
  L0.`id` asc

select
  cr.`email`,
  cr.`id_course`,
  L0.`id` as id_lesson
from
  (
    select distinct
      h.`email`,
      L.`id_course`
    from `lesson` L
      join `history` h on L.`id` = h.`id_lesson`
  ) as cr
  join `lesson` L0 on cr.`id_course` = L0.`id_course`
order by
  cr.`email` asc,
  cr.`id_course` asc,
  L0.`id` asc

-- полная таблица прохождения/непрохождения уроков
select
  L1.`email`,
  L1.`id_course`,
  L1.`id_lesson`,
  H1.`email`
from
  (
  select
    cr.`email`,
    cr.`id_course`,
    L0.`id` as id_lesson
  from
    (
      select distinct
        h.`email`,
        L.`id_course`
      from `lesson` L
        join `history` h on L.`id` = h.`id_lesson`
    ) as cr
    join `lesson` L0 on cr.`id_course` = L0.`id_course`
  ) L1
  left outer join `history` H1
  on
    H1.`email` = L1.`email`
    and H1.`id_lesson` = L1.`id_lesson`
order BY
  L1.`email`,
  L1.`id_course`,
  L1.`id_lesson`

-- полная таблица прохождения/непрохождения уроков
select
  L1.`email`,
  L1.`id_course`,
  L1.`id_lesson`,
  H1.`email`
from
  (
  select
    cr.`email`,
    cr.`id_course`,
    L0.`id` as id_lesson
  from
    (
      select distinct
        h.`email`,
        L.`id_course`
      from `lesson` L
        join `history` h on L.`id` = h.`id_lesson`
    ) as cr
    join `lesson` L0 on cr.`id_course` = L0.`id_course`
  ) L1
  left outer join `history` H1
  on
    H1.`email` = L1.`email`
    and H1.`id_lesson` = L1.`id_lesson`
order BY
  L1.`email`,
  L1.`id_course`,
  L1.`id_lesson`

-- ученик не прошел весь курс
select distinct
  L1.`email`,
  L1.`id_course`
from
  (
  select
    cr.`email`,
    cr.`id_course`,
    L0.`id` as id_lesson
  from
    (
      select distinct
        h.`email`,
        L.`id_course`
      from `lesson` L
        join `history` h on L.`id` = h.`id_lesson`
    ) as cr
    join `lesson` L0 on cr.`id_course` = L0.`id_course`
  ) L1
  left outer join `history` H1
  on
    H1.`email` = L1.`email`
    and H1.`id_lesson` = L1.`id_lesson`
where
  H1.`email` is null

-- ученик не прошел весь курс
select distinct
  L1.`email`,
  L1.`id_course`
from
  (
  select
    cr.`email`,
    cr.`id_course`,
    L0.`id` as id_lesson
  from
    (
      select distinct
        h.`email`,
        L.`id_course`
      from `lesson` L
        join `history` h on L.`id` = h.`id_lesson`
    ) as cr
    join `lesson` L0 on cr.`id_course` = L0.`id_course`
  ) L1
  left outer join `history` H1
  on
    H1.`email` = L1.`email`
    and H1.`id_lesson` = L1.`id_lesson`
where
  H1.`email` is null

-- РЕЗУЛЬТАТ: пользователи прошедшие курс

select
  L3.`email`,
  L3.`id_course`,
  T07.`max_date`
from
  (
  select distinct
    h.`email`,
    l.`id_course`
  from `lesson` l
    join `history` h on l.`id` = h.`id_lesson`
  ) L3 left outer join
  (
  select distinct
    L1.`email`,
    L1.`id_course`
  from
    (
    select
      cr.`email`,
      cr.`id_course`,
      L0.`id` as id_lesson
    from
      (
        select distinct
          h.`email`,
          L.`id_course`
        from `lesson` L
          join `history` h on L.`id` = h.`id_lesson`
      ) as cr
      join `lesson` L0 on cr.`id_course` = L0.`id_course`
    ) L1
    left outer join `history` H1
    on
      H1.`email` = L1.`email`
      and H1.`id_lesson` = L1.`id_lesson`
  where
    H1.`email` is null
  ) L2 on
      L2.`email` = L3.`email`
      and L2.`id_course` = L3.`id_course`
  join
  (
    select
      h.`email`,
      L.`id_course`,
      max(h.`date`) as `max_date`
    from `lesson` L
      join `history` h on L.`id` = h.`id_lesson`
    group by
      h.`email`,
      L.`id_course`
  ) as T07
  on
      T07.`email` = L3.`email`
      and T07.`id_course` = L3.`id_course`
where
 L2.`email` is null
 and L3.`id_course` = 4 -- Ограничиваем одним курсом
;

-- РЕЗУЛЬТАТ: пользователи прошедшие курс

select
  L3.`email`,
  L3.`id_course`,
  T07.`max_date`
from
  (
  select distinct
    h.`email`,
    l.`id_course`
  from `lesson` l
    join `history` h on l.`id` = h.`id_lesson`
  ) L3 left outer join
  (
  select distinct
    L1.`email`,
    L1.`id_course`
  from
    (
    select
      cr.`email`,
      cr.`id_course`,
      L0.`id` as id_lesson
    from
      (
        select distinct
          h.`email`,
          L.`id_course`
        from `lesson` L
          join `history` h on L.`id` = h.`id_lesson`
      ) as cr
      join `lesson` L0 on cr.`id_course` = L0.`id_course`
    ) L1
    left outer join `history` H1
    on
      H1.`email` = L1.`email`
      and H1.`id_lesson` = L1.`id_lesson`
  where
    H1.`email` is null
  ) L2 on
      L2.`email` = L3.`email`
      and L2.`id_course` = L3.`id_course`
  join
  (
    select
      h.`email`,
      L.`id_course`,
      max(h.`date`) as `max_date`
    from `lesson` L
      join `history` h on L.`id` = h.`id_lesson`
    group by
      h.`email`,
      L.`id_course`
  ) as T07
  on
      T07.`email` = L3.`email`
      and T07.`id_course` = L3.`id_course`
where
 L2.`email` is null
 and L3.`id_course` = 4 -- Ограничиваем одним курсом
;

SELECT l.id_course, h.email, MAX(h.date)
FROM lesson l
LEFT JOIN history h ON h.id_lesson = l.id
GROUP BY l.id_course, h.email
HAVING SUM(h.id_lesson IS NULL) = 0

SELECT l.id_course, h.email, MAX(h.date)
FROM lesson l
LEFT JOIN history h ON h.id_lesson = l.id
GROUP BY l.id_course, h.email
HAVING SUM(h.id_lesson IS NULL) = 0