1SELECT
2  metric,
3  MAX(
4    CASE
5      WHEN st_abbr = 'NM' THEN value
6    END
7  ) AS new_mexico,
8  MAX(
9    CASE
10      WHEN st_abbr = 'NH' THEN value
11    END
12  ) AS new_hampshire
13FROM
14  (
15    SELECT
16      st_abbr,
17      'Poverty Rate (%)' AS metric,
18      2 AS sort_order,
19      ROUND(
20        AVG(
21          CASE
22            WHEN ep_pov150 != -999 THEN ep_pov150
23          END
24        ),
25        1
26      ) AS value
27    FROM
28      "@cdc.social_vulnerability_index.svi_2022_us_tract"
29    WHERE
30      st_abbr IN ('NH', 'NM')
31      AND rpl_themes != -999
32    GROUP BY
33      st_abbr
34    UNION ALL
35    SELECT
36      st_abbr,
37      'Uninsured Rate (%)' AS metric,
38      3 AS sort_order,
39      ROUND(
40        AVG(
41          CASE
42            WHEN ep_uninsur != -999 THEN ep_uninsur
43          END
44        ),
45        1
46      ) AS value
47    FROM
48      "@cdc.social_vulnerability_index.svi_2022_us_tract"
49    WHERE
50      st_abbr IN ('NH', 'NM')
51      AND rpl_themes != -999
52    GROUP BY
53      st_abbr
54    UNION ALL
55    SELECT
56      st_abbr,
57      'No HS Diploma (%)' AS metric,
58      4 AS sort_order,
59      ROUND(
60        AVG(
61          CASE
62            WHEN ep_nohsdp != -999 THEN ep_nohsdp
63          END
64        ),
65        1
66      ) AS value
67    FROM
68      "@cdc.social_vulnerability_index.svi_2022_us_tract"
69    WHERE
70      st_abbr IN ('NH', 'NM')
71      AND rpl_themes != -999
72    GROUP BY
73      st_abbr
74    UNION ALL
75    SELECT
76      st_abbr,
77      'Minority Population (%)' AS metric,
78      5 AS sort_order,
79      ROUND(
80        AVG(
81          CASE
82            WHEN ep_minrty != -999 THEN ep_minrty
83          END
84        ),
85        1
86      ) AS value
87    FROM
88      "@cdc.social_vulnerability_index.svi_2022_us_tract"
89    WHERE
90      st_abbr IN ('NH', 'NM')
91      AND rpl_themes != -999
92    GROUP BY
93      st_abbr
94    UNION ALL
95    SELECT
96      st_abbr,
97      'Unemployment Rate (%)' AS metric,
98      6 AS sort_order,
99      ROUND(
100        AVG(
101          CASE
102            WHEN ep_unemp != -999 THEN ep_unemp
103          END
104        ),
105        1
106      ) AS value
107    FROM
108      "@cdc.social_vulnerability_index.svi_2022_us_tract"
109    WHERE
110      st_abbr IN ('NH', 'NM')
111      AND rpl_themes != -999
112    GROUP BY
113      st_abbr
114    UNION ALL
115    SELECT
116      st_abbr,
117      'Disability Rate (%)' AS metric,
118      7 AS sort_order,
119      ROUND(
120        AVG(
121          CASE
122            WHEN ep_disabl != -999 THEN ep_disabl
123          END
124        ),
125        1
126      ) AS value
127    FROM
128      "@cdc.social_vulnerability_index.svi_2022_us_tract"
129    WHERE
130      st_abbr IN ('NH', 'NM')
131      AND rpl_themes != -999
132    GROUP BY
133      st_abbr
134  )
135GROUP BY
136  metric
137ORDER BY
138  MAX(
139    CASE
140      WHEN st_abbr = 'NM' THEN value
141    END
142  ) DESC