1
2WITH first_regions AS (
3
4 SELECT
5 region_1 AS region,
6 variety,
7 price
8 FROM
9 winemag_p1
10 WHERE
11 region_1 IS NOT NULL AND region_1 != ''
12),
13
14second_regions AS (
15
16 SELECT
17 region_2 AS region,
18 variety,
19 price
20 FROM
21 winemag_p1
22 WHERE
23 region_2 IS NOT NULL AND region_2 != ''
24),
25
26complete_list AS (
27 SELECT * FROM first_regions
28 UNION ALL
29 SELECT * FROM second_regions
30)
31
32
33SELECT
34 region,
35 MIN(variety) AS cheapest_variety,
36 MAX(variety) AS expensivest_variety
37FROM
38 complete_list
39GROUP BY
40 region
41ORDER BY
42 region;
43