WITH cte AS (
  SELECT
    departement.insee_dep AS insee_dep,
    nom_operation,
    id_crte,
    CASE
      WHEN type_structure = 'COM'
      THEN 'Communes'
      WHEN type_structure IN ('CC', 'CA', 'CU', 'METRO', 'MET69', 'EPT')
      THEN 'Intercommunalités'
      WHEN type_structure IN ('PETR', 'POLEM')
      THEN 'Regroupement EPCI'
      WHEN type_structure IN ('SIVU', 'SMO', 'SIVOM', 'SMF')
      THEN 'Syndicat de coopération locale'
      WHEN type_structure IN ('REG', 'COLTER')
      THEN 'Région'
      WHEN type_structure = 'DEP'
      THEN 'Département'
      ELSE 'Autre structure'
    END AS structure
  FROM crte_projets
  LEFT JOIN crte
    ON crte_projets.id_crte = crte.id
  JOIN departement
    ON departement.insee_dep = ANY(
      STRING_TO_ARRAY(crte.insee_deps, ', ')
    )
)
SELECT
  structure,
  COUNT(*) AS repartition
FROM cte
GROUP BY
  structure
ORDER BY
  repartition DESC