MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version()


8.0.22


CREATE TABLE student
(
  s_id INT NOT NULL PRIMARY KEY,
  s_name VARCHAR (100) NOT NULL UNIQUE,
  s_email VARCHAR (255) NOT NULL
);
INSERT INTO student VALUES
(1, 'name_1', 'email_1@domain_1.net'), 
(2, 'name_2', 'email_2@domain_2.net'), 
(3, 'name_3', 'email_3@domain_3.net'),
(4, 'name_4', 'email_3@domain_3.net');
CREATE TABLE event
(
  e_id INT NOT NULL PRIMARY KEY,
  e_name VARCHAR (100) NOT NULL,
  e_date_scheduled DATE NOT NULL
);
INSERT INTO event VALUES
(1, 'event_1', '2020-01-01'),
(2, 'event_2', '2020-02-01'),
(3, 'event_3', '2020-03-01'),
(4, 'event_4', '2020-04-01'),
(5, 'event_5', '2020-05-01'),
(6, 'event_6', '2020-06-01');
CREATE TABLE attendance
(
  -- a_id INT NOT NULL PRIMARY KEY,   -- << NOT necessary!
  a_sid INT NOT NULL,
  a_eid INT NOT NULL,
  CONSTRAINT a_s_e_pk PRIMARY KEY (a_sid, a_eid),
  CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id),
  CONSTRAINT a_eid_fk  FOREIGN KEY (a_eid) REFERENCES event(e_id)
);
INSERT INTO attendance VALUES
(1, 1),
(1, 2),
(1, 3),
(1, 4),


(2, 2),
(2, 3),
(2, 5),

(3, 5),
(3, 6);


  SELECT s.s_id, s.s_name, s.s_email, COUNT(a.a_sid) AS show_count
  FROM student s
  LEFT JOIN attendance a
    ON a.a_sid = s.s_id
  GROUP BY s.s_id, s.s_name, s.s_email
  ORDER BY s.s_id;


s_id
s_name
s_email
show_count


1
name_1
email_1@domain_1.net
4

2
name_2
email_2@domain_2.net
3

3
name_3
email_3@domain_3.net
2

4
name_4
email_3@domain_3.net
0


SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
  ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
FROM 
  student s2
  LEFT JOIN attendance a2 
    ON s2.s_id = a2.a_sid
  LEFT JOIN event e
    ON a2.a_eid = e.e_id
ORDER BY s2.s_id, e.e_date_scheduled DESC;


sid
eid
eds
enam
rn


1
4
2020-04-01
event_4
1

1
3
2020-03-01
event_3
2

1
2
2020-02-01
event_2
3

1
1
2020-01-01
event_1
4

2
5
2020-05-01
event_5
1

2
3
2020-03-01
event_3
2

2
2
2020-02-01
event_2
3

3
6
2020-06-01
event_6
1

3
5
2020-05-01
event_5
2

4
null
null
null
1


SELECT sid, eid, eds, enam
FROM
(
SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
  ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
FROM 
  student s2
  LEFT JOIN attendance a2 
    ON s2.s_id = a2.a_sid
  LEFT JOIN event e
    ON a2.a_eid = e.e_id
ORDER BY s2.s_id, e.e_date_scheduled DESC
) AS t
WHERE t.rn = 1; 


sid
eid
eds
enam


1
4
2020-04-01
event_4

2
5
2020-05-01
event_5

3
6
2020-06-01
event_6

4
null
null
null


SET PROFILING = 1;
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, 
       COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event"
FROM
  student s
  LEFT JOIN attendance a
    ON s.s_id = a.a_sid
  JOIN
  (

    SELECT sid, eid, eds, enam
    FROM
    (
      SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
      ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
      FROM 
        student s2
      LEFT JOIN attendance a2 
        ON s2.s_id = a2.a_sid
      LEFT JOIN event e
        ON a2.a_eid = e.e_id
    ORDER BY s2.s_id, e.e_date_scheduled DESC
  ) AS t
  WHERE t.rn = 1
) AS t2
ON s.s_id = t2.sid
GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam
ORDER BY s.s_id, t2.eds  -- << Can invert depending on requirements


sid
snam
smail
a_cnt
eds
Latest event


1
name_1
email_1@domain_1.net
4
2020-04-01
event_4

2
name_2
email_2@domain_2.net
3
2020-05-01
event_5

3
name_3
email_3@domain_3.net
2
2020-06-01
event_6

4
name_4
email_3@domain_3.net
0
null
-----


SHOW PROFILES;


Query_ID
Duration
Query


1
0.00111600
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, 
       COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event"
FROM
  student s
  LEFT JOIN attendance a
    ON s.s_id = a.a_sid
  JOIN
  (

    SELECT sid, eid, eds, enam
    FROM
    (
      SELECT s2.s_id AS sid, e


EXPLAIN ANALYZE
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, 
       COUNT(a.a_sid) AS a_cnt, t2.eds, COALESCE(t2.enam, '-----') AS "Latest event"
FROM
  student s
  LEFT JOIN attendance a
    ON s.s_id = a.a_sid
  JOIN
  (

    SELECT sid, eid, eds, enam
    FROM
    (
      SELECT s2.s_id AS sid, e.e_id AS eid, e.e_date_scheduled AS eds, e.e_name AS enam,
      ROW_NUMBER() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id, e.e_id DESC) AS rn
      FROM 
        student s2
      LEFT JOIN attendance a2 
        ON s2.s_id = a2.a_sid
      LEFT JOIN event e
        ON a2.a_eid = e.e_id
    ORDER BY s2.s_id, e.e_date_scheduled DESC
  ) AS t
  WHERE t.rn = 1
) AS t2
ON s.s_id = t2.sid
GROUP BY s.s_id, s.s_name, s.s_email, t2.eds, t2.enam
ORDER BY s.s_id, t2.eds  -- << Can invert depending on requirements


EXPLAIN


-> Sort: s.s_id, t2.eds  (actual time=0.277..0.278 rows=4 loops=1)
    -> Table scan on <temporary>  (actual time=0.001..0.001 rows=4 loops=1)
        -> Aggregate using temporary table  (actual time=0.265..0.266 rows=4 loops=1)
            -> Nested loop left join  (actual time=0.161..0.198 rows=10 loops=1)
                -> Nested loop inner join  (actual time=0.155..0.172 rows=4 loops=1)
                    -> Filter: (t.rn = 1)  (actual time=0.147..0.151 rows=4 loops=1)
                        -> Table scan on t  (cost=3.85 rows=12) (actual time=0.000..0.002 rows=10 loops=1)
                            -> Materialize  (actual time=0.146..0.149 rows=10 loops=1)
                                -> Sort: s2.s_id, e.e_date_scheduled DESC  (actual time=0.133..0.134 rows=10 loops=1)
                                    -> Table scan on <temporary>  (actual time=0.004..0.005 rows=10 loops=1)
                                        -> Temporary table  (actual time=0.123..0.125 rows=10 loops=1)
                                            -> Window aggregate: row_number() OVER (PARTITION BY s2.s_id ORDER BY s2.s_id,e.e_id desc )   (actual time=0.093..0.101 rows=10 loops=1)
                                                -> Sort: s2.s_id, s2.s_id, e.e_id DESC  (actual time=0.090..0.092 rows=10 loops=1)
                                                    -> Stream results  (cost=7.05 rows=12) (actual time=0.027..0.077 rows=10 loops=1)
                                                        -> Nested loop left join  (cost=7.05 rows=12) (actual time=0.024..0.068 rows=10 loops=1)
                                                            -> Nested loop left join  (cost=2.85 rows=12) (actual time=0.019..0.040 rows=10 loops=1)
                                                                -> Index scan on s2 using s_name  (cost=0.65 rows=4) (actual time=0.011..0.014 rows=4 loops=1)
                                                                -> Index lookup on a2 using PRIMARY (a_sid=s2.s_id)  (cost=0.33 rows=3) (actual time=0.004..0.006 rows=2 loops=4)
                                                            -> Single-row index lookup on e using PRIMARY (e_id=a2.a_eid)  (cost=0.26 rows=1) (actual time=0.002..0.003 rows=1 loops=10)
                    -> Single-row index lookup on s using PRIMARY (s_id=t.sid)  (cost=0.33 rows=1) (actual time=0.005..0.005 rows=1 loops=4)
                -> Index lookup on a using PRIMARY (a_sid=t.sid)  (cost=0.50 rows=3) (actual time=0.003..0.005 rows=2 loops=4)
s_id	s_name	s_email	show_count
1	name_1	email_1@domain_1.net	4
2	name_2	email_2@domain_2.net	3
3	name_3	email_3@domain_3.net	2
4	name_4	email_3@domain_3.net	0
sid	eid	eds	enam	rn
1	4	2020-04-01	event_4	1
1	3	2020-03-01	event_3	2
1	2	2020-02-01	event_2	3
1	1	2020-01-01	event_1	4
2	5	2020-05-01	event_5	1
2	3	2020-03-01	event_3	2
2	2	2020-02-01	event_2	3
3	6	2020-06-01	event_6	1
3	5	2020-05-01	event_5	2
4	null	null	null	1