1-- How countries responded to major crises (2008, 2020)
2WITH crisis_years AS (
3    SELECT 
4        economy,
5        year,
6        value as gdp_growth,
7        LAG(value, 1) OVER (PARTITION BY economy ORDER BY year) as previous_year,
8        LEAD(value, 1) OVER (PARTITION BY economy ORDER BY year) as next_year,
9        CASE 
10            WHEN year = 2008 THEN 'Financial Crisis'
11            WHEN year = 2020 THEN 'COVID-19'
12            ELSE 'Normal'
13        END as period
14    FROM "@worldbank.world_development_indicators.ny_gdp_mktp_kd_zg"
15    WHERE year IN (2007, 2008, 2009, 2019, 2020, 2021)
16        AND economy IN ('CHN', 'IND', 'VNM', 'IDN', 'KOR', 'SGP', 
17                        'USA', 'JPN', 'DEU', 'BRA', 'THA', 'MYS')
18        AND value IS NOT NULL
19)
20SELECT 
21    economy,
22    MAX(CASE WHEN year = 2008 THEN gdp_growth END) as crisis_2008,
23    MAX(CASE WHEN year = 2009 THEN gdp_growth END) as recovery_2009,
24    MAX(CASE WHEN year = 2020 THEN gdp_growth END) as crisis_2020,
25    MAX(CASE WHEN year = 2021 THEN gdp_growth END) as recovery_2021,
26    (MAX(CASE WHEN year = 2009 THEN gdp_growth END) - 
27     MAX(CASE WHEN year = 2008 THEN gdp_growth END)) as bounce_2008,
28    (MAX(CASE WHEN year = 2021 THEN gdp_growth END) - 
29     MAX(CASE WHEN year = 2020 THEN gdp_growth END)) as bounce_2020
30FROM crisis_years
31GROUP BY economy
32ORDER BY bounce_2020 DESC