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