MySQL 8.0

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

CREATE TABLE empOff (ManagerID INTEGER,
                     EmpID     TINYTEXT,
                     EmpOff1   TINYTEXT,
                     EmpOff2   TINYTEXT);
INSERT INTO empOff
    (ManagerID, EmpID, EmpOff1, EmpOff2)
VALUES
    (1, 'a', 'fri', 'sat'),
    (1, 'b', 'sat', 'sun'),
    (1, 'c', 'sun', 'mon'), 
    (2, 'd', 'mon', 'tue'),
    (2, 'e', 'wed', 'thu'),
    (1, 'f', 'wed', 'thu'),
    (1, 'h', 'wed', 'fri'), -- extra
    (1, 'i', 'mon', 'sun'), -- extra
    (2, 'g', 'mon', 'tue');
SELECT * FROM empOff;


ManagerID
EmpID
EmpOff1
EmpOff2


1
a
fri
sat

1
b
sat
sun

1
c
sun
mon

2
d
mon
tue

2
e
wed
thu

1
f
wed
thu

1
h
wed
fri

1
i
mon
sun

2
g
mon
tue


-- normalizing the table into empOffNorm
CREATE TABLE empOffNorm AS
SELECT ManagerID, EmpID, EmpOff1 AS empOffDay
  FROM empOff
UNION
SELECT ManagerID, EmpID, EmpOff2 AS empOffDay
  FROM empOff;
SELECT * FROM empOffNorm


ManagerID
EmpID
empOffDay


1
a
fri

1
b
sat

1
c
sun

2
d
mon

2
e
wed

1
f
wed

1
h
wed

1
i
mon

2
g
mon

1
a
sat

1
b
sun

1
c
mon

2
d
tue

2
e
thu

1
f
thu

1
h
fri

1
i
sun

2
g
tue


-- group the data to count the occurence of each day for the managerIds
SELECT ManagerID, EmpOffDay, count(1) AS cnt
  FROM empOffNorm
 GROUP BY ManagerID, EmpOffDay
 ORDER BY ManagerID, cnt DESC


ManagerID
EmpOffDay
cnt


1
sun
3

1
fri
2

1
sat
2

1
wed
2

1
mon
2

1
thu
1

2
mon
2

2
tue
2

2
wed
1

2
thu
1


-- based on the grouping and ordering, fetch the first 2 rows for a ManagerId
SELECT ManagerID, EmpOffDay, count(1) AS cnt
  FROM empOffNorm
 WHERE managerid = 1
 GROUP BY ManagerID, EmpOffDay
 ORDER BY cnt DESC, ManagerId
 LIMIT 2


ManagerID
EmpOffDay
cnt


1
sun
3

1
fri
2


-- create a table based on the grouping. It will be easier to play with
CREATE TABLE grouped AS
SELECT ManagerID, EmpOffDay, count(1) AS cnt
  FROM empOffNorm
 GROUP BY ManagerID, EmpOffDay
 ORDER BY ManagerID, cnt DESC
SELECT * FROM grouped


ManagerID
EmpOffDay
cnt


1
sun
3

1
fri
2

1
sat
2

1
wed
2

1
mon
2

1
thu
1

2
mon
2

2
tue
2

2
wed
1

2
thu
1


-- create variables to count the rownumbers, but let it restart with every new managerid (ManId_RowNo)
SELECT @rownum := @rownum + 1 AS RowNo,
       @manrownum := IF(@ManId = managerid, @manrownum + 1, 1) AS ManId_RowNo,
       @ManId := ManagerID AS Dummy_ManId,
       ManagerID,
       EmpOffDay,
       cnt
  FROM grouped
  JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables;


RowNo
ManId_RowNo
Dummy_ManId
ManagerID
EmpOffDay
cnt


1
1
1
1
sun
3

2
2
1
1
fri
2

3
3
1
1
sat
2

4
4
1
1
wed
2

5
5
1
1
mon
2

6
6
1
1
thu
1

7
1
2
2
mon
2

8
2
2
2
tue
2

9
3
2
2
wed
1

10
4
2
2
thu
1


-- fetch the top 2 rows for every manager
SELECT ManagerID, EmpOffDay, cnt
  FROM (
        SELECT @rownum := @rownum + 1 AS RowNo,
               @manrownum := IF(@manid = ManagerID, @manrownum + 1, 1) AS ManId_RowNo,
               @ManId := ManagerID AS Dummy_ManId,
               ManagerID,
               EmpOffDay,
               cnt
          FROM grouped
          JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables
       ) AS rownumbered 
WHERE ManId_RowNo < 3; -- here the limit 2 is applied for every ManangerId


ManagerID
EmpOffDay
cnt


1
sun
3

1
fri
2

2
mon
2

2
tue
2


-- insert the original SQL for the grouped table
SELECT @rownum := @rownum + 1 AS RowNo,
       @manrownum := IF(@ManId = ManagerID, @manrownum + 1, 1) AS ManId_RowNo,
       @ManId := ManagerID AS Dummy_ManId,
       ManagerID,
       EmpOffDay,
       cnt
  FROM (
        SELECT ManagerID, EmpOffDay, count(1) AS cnt
          FROM empOffNorm
         GROUP BY ManagerID, EmpOffDay
         ORDER BY ManagerID, cnt DESC
       ) AS grouped
  JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables;


RowNo
ManId_RowNo
Dummy_ManId
ManagerID
EmpOffDay
cnt


1
1
1
1
sun
3

2
2
1
1
fri
2

3
3
1
1
sat
2

4
4
1
1
wed
2

5
5
1
1
mon
2

6
6
1
1
thu
1

7
1
2
2
mon
2

8
2
2
2
tue
2

9
3
2
2
wed
1

10
4
2
2
thu
1


-- insert the original SQL for the empOffNorm table
SELECT @rownum := @rownum + 1 AS RowNo,
       @manrownum := IF(@ManId = ManagerID, @manrownum + 1, 1) AS ManId_RowNo,
       @ManId := ManagerID AS Dummy_ManId,
       ManagerID,
       EmpOffDay,
       cnt
  FROM (
        SELECT ManagerID, EmpOffDay, count(1) AS cnt
          FROM (SELECT ManagerID, EmpID, EmpOff1 AS empOffDay
                 FROM empOff
                UNION
               SELECT ManagerID, EmpID, EmpOff2 AS empOffDay
                 FROM empOff) AS empOffDayNorm
         GROUP BY ManagerID, EmpOffDay
         ORDER BY ManagerID, cnt DESC
       ) AS grouped
  JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables;


RowNo
ManId_RowNo
Dummy_ManId
ManagerID
EmpOffDay
cnt


1
1
1
1
sun
3

2
2
1
1
fri
2

3
3
1
1
sat
2

4
4
1
1
wed
2

5
5
1
1
mon
2

6
6
1
1
thu
1

7
1
2
2
mon
2

8
2
2
2
tue
2

9
3
2
2
wed
1

10
4
2
2
thu
1


-- fetch the top 2 rows for every manager
SELECT ManagerID, EmpOffDay, cnt
  FROM (
        SELECT @rownum := @rownum + 1 AS RowNo,
               @manrownum := if(@ManId = ManagerID, @manrownum + 1, 1) AS ManId_RowNo,
               @ManId := ManagerID AS Dummy_ManId,
               ManagerID,
               EmpOffDay,
               cnt
          FROM (
                SELECT ManagerID, EmpOffDay, count(1) AS cnt
                  FROM (SELECT ManagerID, EmpID, EmpOff1 AS empOffDay
                         FROM empOff
                        UNION
                       SELECT ManagerID, EmpID, EmpOff2 AS empOffDay
                         FROM empOff) AS empOffDayNorm
                 GROUP BY ManagerID, EmpOffDay
                 ORDER BY ManagerID, cnt DESC
               ) AS grouped
          JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables
       ) AS normed_grouped_sorted_rownumbed
 WHERE ManId_RowNo < 3; -- here the limit 2 is applied for every ManangerId


ManagerID
EmpOffDay
cnt


1
sun
3

1
fri
2

2
mon
2

2
tue
2


-- table for de-normalizing SQL. The exact query as above:
CREATE TABLE denorm AS 
SELECT ManagerID, EmpOffDay, cnt
  FROM (
        SELECT @rownum := @rownum + 1 AS RowNo,
               @manrownum := IF(@ManId = ManagerID, @manrownum + 1, 1) AS ManId_RowNo,
               @ManId := ManagerID AS Dummy_ManId,
               ManagerID,
               EmpOffDay,
               cnt
          FROM (
                SELECT ManagerID, EmpOffDay, count(1) AS cnt
                  FROM (SELECT ManagerID, EmpID, EmpOff1 AS empOffDay
                         FROM empOff
                        UNION
                       SELECT ManagerID, EmpID, EmpOff2 AS empOffDay
                         FROM empOff) AS empOffDayNorm
                 GROUP BY ManagerID, EmpOffDay
                 ORDER BY ManagerID, cnt DESC
               ) AS grouped
          JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables
       ) AS normed_grouped_sorted_rownumbed
 WHERE ManId_RowNo < 3; -- here the limit 2 is applied for every ManangerId
SELECT * FROM denorm


ManagerID
EmpOffDay
cnt


1
sun
3

1
fri
2

2
mon
2

2
tue
2


-- denormalized result:
SELECT denorm1.ManagerID, denorm1.EmpOffDay, denorm2.EmpOffDay, denorm1.cnt, denorm2.cnt
  FROM denorm AS denorm1
  JOIN denorm AS denorm2 ON denorm1.ManagerID = denorm2.ManagerID
                        AND denorm1.EmpOffDay < denorm2.EmpOffDay


ManagerID
EmpOffDay
EmpOffDay
cnt
cnt


1
fri
sun
2
3

2
mon
tue
2
2


-- denormalized result without the table creation:
SELECT denorm1.ManagerID, denorm1.EmpOffDay, denorm2.EmpOffDay, denorm1.cnt, denorm2.cnt
  FROM (SELECT ManagerID, EmpOffDay, cnt
          FROM (
                SELECT @rownum := @rownum + 1 AS RowNo,
                       @manrownum := if(@ManId = ManagerID, @manrownum + 1, 1) AS ManId_RowNo,
                       @ManId := ManagerID AS Dummy_ManId,
                       ManagerID,
                       EmpOffDay,
                       cnt
                  FROM (
                        SELECT ManagerID, EmpOffDay, count(1) AS cnt
                          FROM (SELECT ManagerID, EmpID, EmpOff1 AS empOffDay
                                 FROM empOff
                                UNION
                               SELECT ManagerID, EmpID, EmpOff2 AS empOffDay
                                 FROM empOff) AS empOffDayNorm
                         GROUP BY ManagerID, EmpOffDay
                         ORDER BY ManagerID, cnt DESC
                       ) AS grouped
                  JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables
               ) AS normed_grouped_sorted_rownumbed
         WHERE ManId_RowNo < 3) AS denorm1
  JOIN (SELECT ManagerID, EmpOffDay, cnt
          FROM (
                SELECT @rownum := @rownum + 1 AS RowNo,
                       @manrownum := if(@ManId = ManagerID, @manrownum + 1, 1) AS ManId_RowNo,
                       @ManId := ManagerID AS Dummy_ManId,
                       ManagerID,
                       EmpOffDay,
                       cnt
                  FROM (
                        SELECT ManagerID, EmpOffDay, count(1) AS cnt
                          FROM (SELECT ManagerID, EmpID, EmpOff1 AS empOffDay
                                 FROM empOff
                                UNION
                               SELECT ManagerID, EmpID, EmpOff2 AS empOffDay
                                 FROM empOff) AS empOffDayNorm
                         GROUP BY ManagerID, EmpOffDay
                         ORDER BY managerID, cnt DESC
                       ) AS grouped
                  JOIN (SELECT @rownum:=0, @manrownum:=0, @ManId:='') variables
               ) AS normed_grouped_sorted_rownumbed
         WHERE ManId_RowNo < 3) AS denorm2
    ON denorm1.ManagerID = denorm2.ManagerID
   AND denorm1.EmpOffDay < denorm2.EmpOffDay


ManagerID
EmpOffDay
EmpOffDay
cnt
cnt


1
fri
sun
2
3

2
mon
tue
2
2


SELECT ManagerID, EmpOffDay, count(1) AS cnt
  FROM empOffNorm eon
 WHERE EXISTS (SELECT 1
                FROM (SELECT ManagerID, EmpOffDay, count(1) AS cnt
                        FROM empOffNorm wrapped
                       WHERE wrapped.ManagerID = eon.ManagerID -- this particular id
                       GROUP BY ManagerID, EmpOffDay
                       ORDER BY cnt DESC, ManagerId
                       LIMIT 2
                     ) AS limited -- always only 2 rows
               WHERE limited.ManagerID = eon.ManagerID
                 AND limited.EmpOffDay = eon.EmpOffDay
              )
 GROUP BY ManagerID, EmpOffDay
 ORDER BY cnt DESC, ManagerID


ManagerID
EmpOffDay
cnt


1
sun
3

1
fri
2

2
mon
2

2
tue
2

ManagerID	EmpID	EmpOff1	EmpOff2
1	a	fri	sat
1	b	sat	sun
1	c	sun	mon
2	d	mon	tue
2	e	wed	thu
1	f	wed	thu
1	h	wed	fri
1	i	mon	sun
2	g	mon	tue

ManagerID	EmpID	empOffDay
1	a	fri
1	b	sat
1	c	sun
2	d	mon
2	e	wed
1	f	wed
1	h	wed
1	i	mon
2	g	mon
1	a	sat
1	b	sun
1	c	mon
2	d	tue
2	e	thu
1	f	thu
1	h	fri
1	i	sun
2	g	tue

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

ManagerID	EmpID	EmpOff1	EmpOff2
1	a	fri	sat
1	b	sat	sun
1	c	sun	mon
2	d	mon	tue
2	e	wed	thu
1	f	wed	thu
1	h	wed	fri
1	i	mon	sun
2	g	mon	tue

ManagerID	EmpID	empOffDay
1	a	fri
1	b	sat
1	c	sun
2	d	mon
2	e	wed
1	f	wed
1	h	wed
1	i	mon
2	g	mon
1	a	sat
1	b	sun
1	c	mon
2	d	tue
2	e	thu
1	f	thu
1	h	fri
1	i	sun
2	g	tue

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

ManagerID	EmpID	EmpOff1	EmpOff2
1	a	fri	sat
1	b	sat	sun
1	c	sun	mon
2	d	mon	tue
2	e	wed	thu
1	f	wed	thu
1	h	wed	fri
1	i	mon	sun
2	g	mon	tue

ManagerID	EmpID	empOffDay
1	a	fri
1	b	sat
1	c	sun
2	d	mon
2	e	wed
1	f	wed
1	h	wed
1	i	mon
2	g	mon
1	a	sat
1	b	sun
1	c	mon
2	d	tue
2	e	thu
1	f	thu
1	h	fri
1	i	sun
2	g	tue

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1

RowNo	ManId_RowNo	Dummy_ManId	ManagerID	EmpOffDay	cnt
1	1	1	1	sun	3
2	2	1	1	fri	2
3	3	1	1	sat	2
4	4	1	1	wed	2
5	5	1	1	mon	2
6	6	1	1	thu	1
7	1	2	2	mon	2
8	2	2	2	tue	2
9	3	2	2	wed	1
10	4	2	2	thu	1