1-- Filter countries with happiness scores below the global average
2 SELECT
3 country,
4 happiness_score,
5 health,
6 family,
7 freedom,
8 government_trust,
9 generosity,
10 continent,
11 dystopia_residual,
12 "year"
13 FROM @kaggle.eliasturk_world_happiness_based_on_cpi_20152020.worldhappiness_corruption_2015_2020, thresholds
14 WHERE happiness_score < thresholds.avg_happiness_score
15),
16
17factor_analysis AS (
18 SELECT
19 AVG(health) AS low_happiness_avg_health,
20 AVG(family) AS low_happiness_avg_family,
21 AVG(freedom) AS low_happiness_avg_freedom,
22 AVG(government_trust) AS low_happiness_avg_gov_trust,
23 AVG(generosity) AS low_happiness_avg_generosity
24 FROM low_happiness_countries
25)
26
27SELECT
28 'Health' AS factor,
29 abs(low_happiness_avg_health - thresholds.avg_health) AS deviation,
30 low_happiness_avg_health AS low_happiness_avg,
31 thresholds.avg_health AS global_avg
32FROM factor_analysis, thresholds
33UNION ALL
34SELECT
35 'Family' AS factor,
36 abs(low_happiness_avg_family - thresholds.avg_family) AS deviation,
37 low_happiness_avg_family AS low_happiness_avg,
38 thresholds.avg_family AS global_avg
39FROM factor_analysis, thresholds
40UNION ALL
41SELECT
42 'Freedom' AS factor,
43 abs(low_happiness_avg_freedom - thresholds.avg_freedom) AS deviation,
44 low_happiness_avg_freedom AS low_happiness_avg,
45 thresholds.avg_freedom AS global_avg
46FROM factor_analysis, thresholds
47UNION ALL
48SELECT
49 'Government Trust' AS factor,
50 abs(low_happiness_avg_gov_trust - thresholds.avg_gov_trust) AS deviation,
51 low_happiness_avg_gov_trust AS low_happiness_avg,
52 thresholds.avg_gov_trust AS global_avg
53FROM factor_analysis, thresholds
54UNION ALL
55SELECT
56 'Generosity' AS factor,
57 abs(low_happiness_avg_generosity - thresholds.avg_generosity) AS deviation,
58 low_happiness_avg_generosity AS low_happiness_avg,
59 thresholds.avg_generosity AS global_avg
60FROM factor_analysis, thresholds
61ORDER BY deviation DESC