1with perc_female_secondary_education_country_year as (
2SELECT
3    economy, 
4    year1, 
5    value1
6FROM @worldbank.world_development_indicators.se_sec_enrl_fe_zs,
7LATERAL (VALUES
8    ('1960', "yr1960"),
9    ('1961', "yr1961"),
10    ('1962', "yr1962"),
11    ('1963', "yr1963"),
12    ('1964', "yr1964"),
13    ('1965', "yr1965"),
14    ('1966', "yr1966"),
15    ('1967', "yr1967"),
16    ('1968', "yr1968"),
17    ('1969', "yr1969"),
18    ('1970', "yr1970"),
19    ('1971', "yr1971"),
20    ('1972', "yr1972"),
21    ('1973', "yr1973"),
22    ('1974', "yr1974"),
23    ('1975', "yr1975"),
24    ('1976', "yr1976"),
25    ('1977', "yr1977"),
26    ('1978', "yr1978"),
27    ('1979', "yr1979"),
28    ('1980', "yr1980"),
29    ('1981', "yr1981"),
30    ('1982', "yr1982"),
31    ('1983', "yr1983"),
32    ('1984', "yr1984"),
33    ('1985', "yr1985"),
34    ('1986', "yr1986"),
35    ('1987', "yr1987"),
36    ('1988', "yr1988"),
37    ('1989', "yr1989"),
38    ('1990', "yr1990"),
39    ('1991', "yr1991"),
40    ('1992', "yr1992"),
41    ('1993', "yr1993"),
42    ('1994', "yr1994"),
43    ('1995', "yr1995"),
44    ('1996', "yr1996"),
45    ('1997', "yr1997"),
46    ('1998', "yr1998"),
47    ('1999', "yr1999"),
48    ('2000', "yr2000"),
49    ('2001', "yr2001"),
50    ('2002', "yr2002"),
51    ('2003', "yr2003"),
52    ('2004', "yr2004"),
53    ('2005', "yr2005"),
54    ('2006', "yr2006"),
55    ('2007', "yr2007"),
56    ('2008', "yr2008"),
57    ('2009', "yr2009"),
58    ('2010', "yr2010"),
59    ('2011', "yr2011"),
60    ('2012', "yr2012"),
61    ('2013', "yr2013"),
62    ('2014', "yr2014"),
63    ('2015', "yr2015"),
64    ('2016', "yr2016"),
65    ('2017', "yr2017"),
66    ('2018', "yr2018"),
67    ('2019', "yr2019"),
68    ('2020', "yr2020"),
69    ('2021', "yr2021"),
70    ('2022', "yr2022")
71) AS year_values(year1, value1)
72)
73select economy, year1 as "year", avg(value1) as perc_female_secondary_education
74from perc_female_secondary_education_country_year
75where year1 >= '1970' AND year1 <= '2020'
76group by 1,2
77order by 1