1-- Latest comprehensive snapshot for portfolio allocation decisions
2WITH latest_year AS (
3    SELECT MAX(year) as max_year 
4    FROM "@worldbank.world_development_indicators.ny_gdp_mktp_kd_zg"
5),
6recent_5y AS (
7    SELECT 
8        economy,
9        AVG(value) as avg_growth_5y,
10        STDDEV(value) as volatility_5y
11    FROM "@worldbank.world_development_indicators.ny_gdp_mktp_kd_zg"
12    WHERE year >= (SELECT max_year - 5 FROM latest_year)
13        AND value IS NOT NULL
14    GROUP BY economy
15),
16latest_metrics AS (
17    SELECT DISTINCT
18        g.economy,
19        g.year,
20        g.value as latest_gdp_growth,
21        pc.value as gdp_per_capita,
22        gdp_current.value as gdp_current_usd,
23        fdi.value as fdi_pct_gdp,
24        trade.value as trade_pct_gdp,
25        pop.value as population,
26        mkt_cap.value as market_cap_pct_gdp,
27        credit.value as domestic_credit_pct_gdp
28    FROM "@worldbank.world_development_indicators.ny_gdp_mktp_kd_zg" g
29    LEFT JOIN "@worldbank.world_development_indicators.ny_gdp_pcap_kd" pc
30        ON g.economy = pc.economy AND g.year = pc.year
31    LEFT JOIN "@worldbank.world_development_indicators.ny_gdp_mktp_cd" gdp_current
32        ON g.economy = gdp_current.economy AND g.year = gdp_current.year
33    LEFT JOIN "@worldbank.world_development_indicators.bx_klt_dinv_wd_gd_zs" fdi
34        ON g.economy = fdi.economy AND g.year = fdi.year
35    LEFT JOIN "@worldbank.world_development_indicators.ne_trd_gnfs_zs" trade
36        ON g.economy = trade.economy AND g.year = trade.year
37    LEFT JOIN "@worldbank.world_development_indicators.sp_pop_totl" pop
38        ON g.economy = pop.economy AND g.year = pop.year
39    LEFT JOIN "@worldbank.world_development_indicators.cm_mkt_lcap_gd_zs" mkt_cap
40        ON g.economy = mkt_cap.economy AND g.year = mkt_cap.year
41    LEFT JOIN "@worldbank.world_development_indicators.fs_ast_prvt_gd_zs" credit
42        ON g.economy = credit.economy AND g.year = credit.year
43    WHERE g.year = (SELECT max_year FROM latest_year)
44)
45SELECT 
46    lm.*,
47    r5.avg_growth_5y,
48    r5.volatility_5y,
49    (r5.avg_growth_5y / NULLIF(r5.volatility_5y, 0)) as sharpe_ratio_5y,
50    (lm.gdp_current_usd / 1e9) as gdp_billions_usd,
51    (lm.population / 1e6) as population_millions,
52    CASE 
53        WHEN lm.gdp_per_capita > 30000 THEN 'Developed'
54        WHEN lm.gdp_per_capita > 12000 THEN 'Upper Middle Income'
55        WHEN lm.gdp_per_capita > 4000 THEN 'Lower Middle Income'
56        ELSE 'Low Income'
57    END as income_category,
58    CASE 
59        WHEN r5.avg_growth_5y > 6 AND r5.volatility_5y < 3 THEN 'A - High Growth, Low Risk'
60        WHEN r5.avg_growth_5y > 6 THEN 'B+ - High Growth, Moderate Risk'
61        WHEN r5.avg_growth_5y > 4 AND r5.volatility_5y < 3 THEN 'B - Moderate Growth, Low Risk'
62        WHEN r5.avg_growth_5y > 3 THEN 'C+ - Moderate Growth'
63        ELSE 'C - Low Growth'
64    END as investment_grade
65FROM latest_metrics lm
66LEFT JOIN recent_5y r5 ON lm.economy = r5.economy
67WHERE lm.economy IN ('CHN', 'IND', 'VNM', 'IDN', 'KOR', 'SGP', 
68                      'USA', 'JPN', 'DEU', 'BRA', 'MEX', 'THA', 'MYS', 'PHL', 'POL', 'TUR')
69ORDER BY investment_grade, r5.avg_growth_5y DESC