WITH latest_data AS (
    SELECT
        country,
        year,
        recycling_recommended,
        incineration_recommended,
        ROW_NUMBER() OVER (PARTITION BY country ORDER BY year DESC) AS rn
    FROM @owid.vaccine_safety.owid_vaccine_safety
)
SELECT
    practice,
    percentage_icineration,
    percentage_recycling
FROM (
    SELECT
        'Recycling Recommended' AS practice,
        --SUM(CASE WHEN LOWER(ld.recycling_recommended) = 'yes' THEN 1 ELSE 0 END) AS countries_recommending,
        --COUNT(DISTINCT ld.country) AS total_countries,
        ROUND(
            SUM(CASE WHEN LOWER(ld.recycling_recommended) = 'yes' THEN 1 ELSE 0 END) * 100.0 /
            COUNT(DISTINCT ld.country), 2
        ) AS percentage_recycling,
        null as percentage_icineration
    FROM latest_data ld
    WHERE rn = 1

    UNION ALL

    SELECT
        'Incineration Recommended' AS practice,
        --SUM(CASE WHEN LOWER(ld.incineration_recommended) = 'yes' THEN 1 ELSE 0 END),
        --COUNT(DISTINCT ld.country),
        null as percentage_recycling,
        ROUND(
            SUM(CASE WHEN LOWER(ld.incineration_recommended) = 'yes' THEN 1 ELSE 0 END) * 100.0 /
            COUNT(DISTINCT ld.country), 2
        ) as percentage_icineration
    FROM latest_data ld
    WHERE rn = 1
)
order by practice