1-- Calculate growth stability metrics for investment risk assessment
2WITH yearly_growth AS (
3    SELECT 
4        economy,
5        year,
6        value as gdp_growth
7    FROM "@worldbank.world_development_indicators.ny_gdp_mktp_kd_zg"
8    WHERE year >= 2010 AND year <= 2024
9        AND economy IN ('CHN', 'IND', 'VNM', 'IDN', 'KOR', 'SGP', 
10                        'USA', 'JPN', 'DEU', 'BRA', 'MEX', 'THA', 'MYS', 'PHL')
11        AND value IS NOT NULL
12),
13growth_stats AS (
14    SELECT 
15        economy,
16        AVG(gdp_growth) as avg_growth,
17        STDDEV(gdp_growth) as volatility,
18        MIN(gdp_growth) as worst_year,
19        MAX(gdp_growth) as best_year,
20        COUNT(*) as data_points,
21        SUM(CASE WHEN gdp_growth < 0 THEN 1 ELSE 0 END) as negative_years
22    FROM yearly_growth
23    GROUP BY economy
24)
25SELECT 
26    gs.*,
27    (gs.avg_growth / NULLIF(gs.volatility, 0)) as sharpe_ratio,
28    (gs.negative_years * 100.0 / gs.data_points) as pct_negative_years,
29    pc.value as latest_gdp_per_capita
30FROM growth_stats gs
31LEFT JOIN "@worldbank.world_development_indicators.ny_gdp_pcap_kd" pc
32    ON gs.economy = pc.economy AND pc.year = 2024
33ORDER BY sharpe_ratio DESC NULLS LAST