WITH consultas_especialidades AS (
SELECT E.id_especialidade,
       E.designacao,
       TO_CHAR(C.data_hora,'DD-MM-YYYY') AS DATA,
       COUNT(*) AS nr_consultas
FROM consultas C
        INNER JOIN medicos M ON C.id_medico = M.id_medico
        INNER JOIN especialidades E ON M.id_especialidade = E.id_especialidade
GROUP BY E.id_especialidade, E.designacao, TO_CHAR(C.data_hora,'DD-MM-YYYY'))
SELECT CE1.id_especialidade,
       CE1.designacao,
       CE1.data
FROM consultas_especialidades CE1
WHERE nr_consultas = (SELECT MAX(CE2.nr_consultas)
                      FROM consultas_especialidades CE2
                      WHERE CE2.id_especialidade = CE1.id_especialidade)
ORDER BY CE1.designacao ASC;