Share of CDDA sites in major ecosystems by IUCN categories

PREFIX schema: <http://reference.eionet.europa.eu/cdda/schema/>
#tech debt, rework it so the filter on iso3 is applied once only
#
#the filter is not working as such
#FILTER (?iso3 NOT IN ('GRL','SJM','ATF', 'BLM', 'GLP','GUF','MAF','MTQ','MYT','REU','SPM','PYF'))

SELECT 
?major_ecosystem_type
?iucncat

sum(?share) as ?share
sum(?sites_area) as ?sites_area
sql:GROUP_CONCAT_DISTINCT(?site_area_total,' ') as ?site_area_total
0 as ?order
  {
  SELECT
    bif:either(UCASE(?major_ecosystem_type) = 'T', 'Terrestrial ecosystem',
             bif:either(?major_ecosystem_type = 'MT', 'Mixed marine and terrestrial ecosystem',
             bif:either(?major_ecosystem_type = 'M', 'Marine ecosystem',
             'Ecosystem type not available'))) as ?major_ecosystem_type

    bif:either(UCASE(?iucncat) in ('UA', 'NA', 'NOTREPORTED'), 'Not reported', 
          bif:either(UCASE(?iucncat) in ('NOTAPPLICABLE'), 'Not applicable',
          bif:either(UCASE(?iucncat) in ('NOTASSIGNED'), 'Not assigned',
         REPLACE(STR(?iucncat),'A','a')))) as ?iucncat #'IA' -> 'Ia'

    round((sum(xsd:double(?site_area))/?site_area_total)*10000)/100.0 as ?share
    sum(xsd:double(?site_area)) as ?sites_area
    ?site_area_total

  WHERE {
  GRAPH <http://r.eionet.europa.eu/rdfdumps/cdda/cddasites.rdf.gz> 
    {
      ?sites schema:site_code ?site_code .
      ?sites schema:site_area ?site_area .
      ?sites schema:iucncat ?iucncat .
      ?sites schema:iso3 ?iso3 .
      FILTER (?iso3 != 'GRL' && ?iso3 != 'SJM' && ?iso3 != 'ATF' && ?iso3 != 'BLM' && ?iso3 != 'GLP' && ?iso3 != 'GUF' && ?iso3 != 'MAF' && ?iso3 != 'MTQ' && ?iso3 != 'MYT' && ?iso3 != 'REU' && ?iso3 != 'SPM' && ?iso3 != 'PYF')
      ?sites schema:major_ecosystem_type ?major_ecosystem_type .
      {     
        SELECT DISTINCT
          sum(xsd:double(?site_area)) as ?site_area_total
          bif:UCASE(?major_ecosystem_type) as ?major_ecosystem_type
        WHERE {
        GRAPH <http://r.eionet.europa.eu/rdfdumps/cdda/cddasites.rdf.gz> 
          {
            ?sites schema:site_area ?site_area .
            ?sites schema:major_ecosystem_type ?major_ecosystem_type .
            ?sites schema:iso3 ?iso3 .
            FILTER (?iso3 != 'GRL' && ?iso3 != 'SJM' && ?iso3 != 'ATF' && ?iso3 != 'BLM' && ?iso3 != 'GLP' && ?iso3 != 'GUF' && ?iso3 != 'MAF' && ?iso3 != 'MTQ' && ?iso3 != 'MYT' && ?iso3 != 'REU' && ?iso3 != 'SPM' && ?iso3 != 'PYF')
            
          }  
        }
        GROUP BY bif:UCASE(?major_ecosystem_type) ?site_area_total
      }
    }
  }
  GROUP BY UCASE(?iucncat) UCASE(?major_ecosystem_type) ?site_area_total
}
ORDER BY  ?major_ecosystem_type ?order ?iucncat