WITH cte AS (
  WITH cteEpci AS (
    SELECT
      CONCAT(lib_groupement, ' - ', lib_fabp, ' (', annee, ')') AS collectivite,
      "fabriques_prospectives_groupements"."siren_groupement" AS "siren_groupement",
      "fabriques_prospectives_groupements"."nature_juridique" AS "nature_juridique"
    FROM "fabriques_prospectives_groupements"
  )
  SELECT
    cteEpci.*,
    insee_com,
    insee_dep
  FROM cteEpci
  JOIN composition_epci_ept_2024
    ON cteEpci.siren_groupement = composition_epci_ept_2024.insee_epci
  UNION
  SELECT
    cteEpci.*,
    insee_geo AS insee_com,
    commune.insee_dep
  FROM cteEpci
  JOIN groupements
    USING (siren_groupement)
  JOIN commune
    ON insee_geo = commune.insee_com
  WHERE
    cteEpci.nature_juridique = 'COM'
)
SELECT DISTINCT
  (
    collectivite
  )
FROM cte
ORDER BY
  collectivite