SELECT
  cte.lib AS libelle_crte,
  COUNT(DISTINCT crte_operation) AS nb_operations
FROM (
  SELECT
    *,
    CONCAT(nom_operation, id_operation, crte.id) AS crte_operation
  FROM crte_projets
  JOIN crte
    ON crte_projets.id_crte = crte.id
  JOIN (
    SELECT DISTINCT
      insee_epci,
      insee_dep,
      insee_reg
    FROM composition_epci
  ) AS compo_epci
    ON compo_epci.insee_epci = ANY(
      crte.siren_epci
    )
) AS cte
GROUP BY
  libelle_crte
ORDER BY
  nb_operations DESC