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