By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table sports (player int, sport varchar(50), playdate varchar(50))
insert into sports values (1, 'soccer', '4/1'),
(4, 'basketball', '4/2'),
(2, 'basketball', '4/3'),
(3, 'soccer', '4/1'),
(1, 'baseball', '4/2'),
(4, 'basketball', '4/3'),
(5, 'soccer', '4/1'),
(3, 'baseball', '4/2'),
(5, 'soccer', '4/2'),
(2, 'basketball', '4/3'),
(1, 'soccer', '4/4'),
(2, 'baseball', '4/5'),
(3, 'soccer', '4/4'),
(4, 'soccer', '4/5'),
(5, 'soccer', '4/3'),
(5, 'basketball', '4/5')
select * from sports
| player | sport | playdate |
|---|---|---|
| 1 | soccer | 4/1 |
| 4 | basketball | 4/2 |
| 2 | basketball | 4/3 |
| 3 | soccer | 4/1 |
| 1 | baseball | 4/2 |
| 4 | basketball | 4/3 |
| 5 | soccer | 4/1 |
| 3 | baseball | 4/2 |
| 5 | soccer | 4/2 |
| 2 | basketball | 4/3 |
| 1 | soccer | 4/4 |
| 2 | baseball | 4/5 |
| 3 | soccer | 4/4 |
| 4 | soccer | 4/5 |
| 5 | soccer | 4/3 |
| 5 | basketball | 4/5 |
select s.sport,
count(distinct s.playdate) as times_played,
(select max(t.players) from (
select count(distinct s2.player) as players
from sports s2
where s2.sport = s.sport
group by s2.sport, s2.playdate
) t
) as nbr_players
from sports s
group by s.sport
| sport | times_played | nbr_players |
|---|---|---|
| baseball | 2 | 2 |
| basketball | 3 | 2 |
| soccer | 5 | 3 |