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,
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,
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