MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE event_ (
    event_id INT UNSIGNED NOT NULL PRIMARY KEY,
    event_date DATE NOT NULL
);

INSERT INTO event_ VALUES
    ( 21,'2017-01-29'), ( 27,'2018-01-16'), ( 45,'2017-01-27'), ( 49,'2018-01-29'), ( 59,'2017-01-24'),
    ( 71,'2017-01-30'), ( 73,'2017-01-14'), ( 79,'2017-01-16'), (101,'2019-01-03'), (102,'2020-01-12'),
    (119,'2017-01-30'), (122,'2019-01-18'), (144,'2020-01-24'), (159,'2018-01-16'), (164,'2017-01-03'),
    (171,'2018-01-17'), (190,'2018-01-02'), (193,'2018-01-08'), (200,'2019-01-07'), (230,'2017-01-23'),
    (239,'2017-01-06'), (256,'2020-01-01'), (277,'2017-01-13'), (291,'2018-01-17'), (328,'2017-01-10'),
    (332,'2018-01-22'), (341,'2018-01-12'), (348,'2019-01-17'), (388,'2017-01-05');

Records: 29  Duplicates: 0  Warnings: 0


/* Step 1. GROUP BY YEAR, MONTH, WEEKDAY, and add RANK() */
SELECT
    YEAR(`event_date`) AS `year`,
    MONTH(`event_date`) AS `month`,
    WEEKDAY(`event_date`) AS `day_num`,
    DAYNAME(`event_date`) AS `day_name`,
    COUNT(*) AS `count`,
    RANK() OVER (PARTITION BY YEAR(`event_date`), MONTH(`event_date`) ORDER BY COUNT(*) DESC) AS `rank`
FROM `event_`
GROUP BY `year`, `month`, `day_num`, `day_name`
ORDER BY `year`, `month`, `rank`;


year
month
day_num
day_name
count
rank


2017
1
0
Monday
4
1

2017
1
4
Friday
3
2

2017
1
1
Tuesday
3
2

2017
1
6
Sunday
1
4

2017
1
5
Saturday
1
4

2017
1
3
Thursday
1
4

2018
1
1
Tuesday
3
1

2018
1
0
Monday
3
1

2018
1
2
Wednesday
2
3

2018
1
4
Friday
1
4

2019
1
3
Thursday
2
1

2019
1
4
Friday
1
2

2019
1
0
Monday
1
2

2020
1
6
Sunday
1
1

2020
1
4
Friday
1
1

2020
1
2
Wednesday
1
1


/* Step 2. Select the days ranked #1 per month and pivot per year */
WITH `ranked` (`year`, `month`, `day_num`, `day_name`, `rank`) AS (
    SELECT
        YEAR(`event_date`) AS `year`,
        MONTH(`event_date`) AS `month`,
        WEEKDAY(`event_date`) AS `day_num`,
        DAYNAME(`event_date`) AS `day_name`,
        RANK() OVER (PARTITION BY YEAR(`event_date`), MONTH(`event_date`) ORDER BY COUNT(*) DESC) AS `rank`
    FROM `event_`
    GROUP BY `year`, `month`, `day_num`, `day_name`
)
SELECT `month`,
    GROUP_CONCAT(IF(`year` = 2017, `day_name`, NULL) ORDER BY `day_num`) AS `2017`,
    GROUP_CONCAT(IF(`year` = 2018, `day_name`, NULL) ORDER BY `day_num`) AS `2018`,
    GROUP_CONCAT(IF(`year` = 2019, `day_name`, NULL) ORDER BY `day_num`) AS `2019`,
    GROUP_CONCAT(IF(`year` = 2020, `day_name`, NULL) ORDER BY `day_num`) AS `2020`
FROM `ranked`
WHERE `rank` = 1
GROUP BY `month`;


month
2017
2018
2019
2020


1
Monday
Monday,Tuesday
Thursday
Wednesday,Friday,Sunday

year	month	day_num	day_name	count	rank
2017	1	0	Monday	4	1
2017	1	4	Friday	3	2
2017	1	1	Tuesday	3	2
2017	1	6	Sunday	1	4
2017	1	5	Saturday	1	4
2017	1	3	Thursday	1	4
2018	1	1	Tuesday	3	1
2018	1	0	Monday	3	1
2018	1	2	Wednesday	2	3
2018	1	4	Friday	1	4
2019	1	3	Thursday	2	1
2019	1	4	Friday	1	2
2019	1	0	Monday	1	2
2020	1	6	Sunday	1	1
2020	1	4	Friday	1	1
2020	1	2	Wednesday	1	1