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, 
  a_sid INT NOT NULL,
  CONSTRAINT a_sid_fk FOREIGN KEY (a_sid) REFERENCES student(s_id),
  a_eid INT NOT NULL,
  CONSTRAINT a_eid_fk  FOREIGN KEY (a_eid) REFERENCES event(e_id)
);
INSERT INTO attendance VALUES
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),


(5, 2, 2),
(6, 2, 3),
(7, 2, 5),

(8, 3, 5),
(9, 3, 6);


  SELECT s.s_id, s.s_name, s.s_email, COUNT(a.a_id) 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_id) 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.00159975
SELECT s.s_id AS sid, s.s_name AS snam, s.s_email AS smail, 
       COUNT(a.a_id) 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_id) 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.295..0.296 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.284..0.285 rows=4 loops=1)
            -> Nested loop left join  (actual time=0.202..0.235 rows=10 loops=1)
                -> Nested loop inner join  (actual time=0.195..0.212 rows=4 loops=1)
                    -> Filter: (t.rn = 1)  (actual time=0.187..0.192 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.186..0.189 rows=10 loops=1)
                                -> Sort: s2.s_id, e.e_date_scheduled DESC  (actual time=0.173..0.174 rows=10 loops=1)
                                    -> Table scan on <temporary>  (actual time=0.001..0.002 rows=10 loops=1)
                                        -> Temporary table  (actual time=0.162..0.164 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.145..0.154 rows=10 loops=1)
                                                -> Sort: s2.s_id, s2.s_id, e.e_id DESC  (actual time=0.141..0.144 rows=10 loops=1)
                                                    -> Stream results  (cost=8.05 rows=12) (actual time=0.037..0.125 rows=10 loops=1)
                                                        -> Nested loop left join  (cost=8.05 rows=12) (actual time=0.035..0.114 rows=10 loops=1)
                                                            -> Nested loop left join  (cost=3.85 rows=12) (actual time=0.029..0.081 rows=10 loops=1)
                                                                -> Index scan on s2 using s_name  (cost=0.65 rows=4) (actual time=0.013..0.015 rows=4 loops=1)
                                                                -> Index lookup on a2 using a_sid_fk (a_sid=s2.s_id)  (cost=0.57 rows=3) (actual time=0.014..0.016 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.003..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.004..0.004 rows=1 loops=4)
                -> Index lookup on a using a_sid_fk (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