1WITH latest_data AS (
2    SELECT
3        country,
4        year,
5        recycling_recommended,
6        incineration_recommended,
7        ROW_NUMBER() OVER (PARTITION BY country ORDER BY year DESC) AS rn
8    FROM @owid.vaccine_safety.owid_vaccine_safety
9)
10SELECT
11    practice,
12    percentage_icineration,
13    percentage_recycling
14FROM (
15    SELECT
16        'Recycling Recommended' AS practice,
17        --SUM(CASE WHEN LOWER(ld.recycling_recommended) = 'yes' THEN 1 ELSE 0 END) AS countries_recommending,
18        --COUNT(DISTINCT ld.country) AS total_countries,
19        ROUND(
20            SUM(CASE WHEN LOWER(ld.recycling_recommended) = 'yes' THEN 1 ELSE 0 END) * 100.0 /
21            COUNT(DISTINCT ld.country), 2
22        ) AS percentage_recycling,
23        null as percentage_icineration
24    FROM latest_data ld
25    WHERE rn = 1
26
27    UNION ALL
28
29    SELECT
30        'Incineration Recommended' AS practice,
31        --SUM(CASE WHEN LOWER(ld.incineration_recommended) = 'yes' THEN 1 ELSE 0 END),
32        --COUNT(DISTINCT ld.country),
33        null as percentage_recycling,
34        ROUND(
35            SUM(CASE WHEN LOWER(ld.incineration_recommended) = 'yes' THEN 1 ELSE 0 END) * 100.0 /
36            COUNT(DISTINCT ld.country), 2
37        ) as percentage_icineration
38    FROM latest_data ld
39    WHERE rn = 1
40)
41order by practice