GV10_2025

SELECT countryCode as "country_code", CAST(countryCode AS VARCHAR) as "countryCode",
    Year,
    season,
    MAX(CASE WHEN qualityNew = 'Excellent' THEN numberOfBathingWaters ELSE 0 END) AS "Excellent",
    MAX(CASE WHEN qualityNew = 'Good' THEN numberOfBathingWaters ELSE 0 END) AS "Good",
    MAX(CASE WHEN qualityNew = 'Poor' THEN numberOfBathingWaters ELSE 0 END) AS "Poor",
    MAX(CASE WHEN qualityNew = 'NoClass' THEN numberOfBathingWaters ELSE 0 END) AS "NoClass",
CASE 
        WHEN (SELECT sum(numberOfBathingWaters)
              FROM [WISE_BWD].[latest].[assessment_BathingWaterStatus_Country] t2
              WHERE t2.countryCode = second.countryCode AND t2.season = 2023 and quality = '1 - Excellent') 
             < 
             (SELECT sum(numberOfBathingWaters)
              FROM [WISE_BWD].[latest].[assessment_BathingWaterStatus_Country] t2
              WHERE t2.countryCode = second.countryCode AND t2.season = 2010 and quality = '1 - Excellent') 
        THEN 'decreased'
        ELSE 'increased'
    END AS "status"
FROM (
select qualityNew, CAST(sum(numberOfBathingWaters) as INTEGER) as "numberOfBathingWaters", season, "Year", countryCode from(select CASE
    WHEN quality = '1 - Excellent' THEN 'Excellent'
    WHEN quality in ('2 - Good', '3 - Good or Sufficient', '3 - Sufficient') THEN 'Good'
    WHEN quality = '4 - Poor' THEN 'Poor'
    WHEN quality = '0 - Not classified' THEN 'NoClass'
END as "qualityNew",numberOfBathingWaters, 
CASE WHEN season = 2023 THEN '2023 (latest)' ELSE CAST(season AS VARCHAR) END as "Year"
, season
, countryCode from [WISE_BWD].[latest].[assessment_BathingWaterStatus_Country]
where season in (2010, 2016, 2023)) as first
group by qualityNew, Year, season, countryCode
) as second
GROUP BY Year, season, countryCode
order by countryCode, Year