WITH thresholds AS (
    SELECT
        AVG(happiness_score) AS avg_happiness_score,
        AVG(health) AS avg_health,
        AVG(family) AS avg_family,
        AVG(freedom) AS avg_freedom,
        AVG(government_trust) AS avg_gov_trust,
        AVG(generosity) AS avg_generosity
    FROM @kaggle.eliasturk_world_happiness_based_on_cpi_20152020.worldhappiness_corruption_2015_2020
),


low_happiness_countries AS (
    -- Filter countries with happiness scores below the global average
    SELECT
        country,
        happiness_score,
        health,
        family,
        freedom,
        government_trust,
        generosity,
        continent,
       dystopia_residual,
        "year"
    FROM @kaggle.eliasturk_world_happiness_based_on_cpi_20152020.worldhappiness_corruption_2015_2020, thresholds
    WHERE happiness_score < thresholds.avg_happiness_score
),

factor_analysis AS (
    SELECT
        AVG(health) AS low_happiness_avg_health,
        AVG(family) AS low_happiness_avg_family,
        AVG(freedom) AS low_happiness_avg_freedom,
        AVG(government_trust) AS low_happiness_avg_gov_trust,
        AVG(generosity) AS low_happiness_avg_generosity
    FROM low_happiness_countries
)

SELECT
    'Health' AS factor,
    abs(low_happiness_avg_health - thresholds.avg_health) AS deviation,
    low_happiness_avg_health AS low_happiness_avg,
    thresholds.avg_health AS global_avg
FROM factor_analysis, thresholds
UNION ALL
SELECT
    'Family' AS factor,
    abs(low_happiness_avg_family - thresholds.avg_family) AS deviation,
    low_happiness_avg_family AS low_happiness_avg,
    thresholds.avg_family AS global_avg
FROM factor_analysis, thresholds
UNION ALL
SELECT
    'Freedom' AS factor,
    abs(low_happiness_avg_freedom - thresholds.avg_freedom) AS deviation,
    low_happiness_avg_freedom AS low_happiness_avg,
    thresholds.avg_freedom AS global_avg
FROM factor_analysis, thresholds
UNION ALL
SELECT
    'Government Trust' AS factor,
    abs(low_happiness_avg_gov_trust - thresholds.avg_gov_trust) AS deviation,
    low_happiness_avg_gov_trust AS low_happiness_avg,
    thresholds.avg_gov_trust AS global_avg
FROM factor_analysis, thresholds
UNION ALL
SELECT
    'Generosity' AS factor,
    abs(low_happiness_avg_generosity - thresholds.avg_generosity) AS deviation,
    low_happiness_avg_generosity AS low_happiness_avg,
    thresholds.avg_generosity AS global_avg
FROM factor_analysis, thresholds
ORDER BY deviation DESC