with infant_mortality as 
(select "year", avg(child_mortality)/10 as percentage_child_mortality_under_five
from @owid.under_five_mortality.owid_under_five_mortality_2
where country='Indonesia' AND "year" >= 1970 and "year" <= 2018
group by 1),
perc_female_secondary_education_country_year as (
SELECT
    economy, 
    year1, 
    value1
FROM @worldbank.world_development_indicators.se_sec_enrl_fe_zs,
LATERAL (VALUES
    ('1960', "yr1960"),
    ('1961', "yr1961"),
    ('1962', "yr1962"),
    ('1963', "yr1963"),
    ('1964', "yr1964"),
    ('1965', "yr1965"),
    ('1966', "yr1966"),
    ('1967', "yr1967"),
    ('1968', "yr1968"),
    ('1969', "yr1969"),
    ('1970', "yr1970"),
    ('1971', "yr1971"),
    ('1972', "yr1972"),
    ('1973', "yr1973"),
    ('1974', "yr1974"),
    ('1975', "yr1975"),
    ('1976', "yr1976"),
    ('1977', "yr1977"),
    ('1978', "yr1978"),
    ('1979', "yr1979"),
    ('1980', "yr1980"),
    ('1981', "yr1981"),
    ('1982', "yr1982"),
    ('1983', "yr1983"),
    ('1984', "yr1984"),
    ('1985', "yr1985"),
    ('1986', "yr1986"),
    ('1987', "yr1987"),
    ('1988', "yr1988"),
    ('1989', "yr1989"),
    ('1990', "yr1990"),
    ('1991', "yr1991"),
    ('1992', "yr1992"),
    ('1993', "yr1993"),
    ('1994', "yr1994"),
    ('1995', "yr1995"),
    ('1996', "yr1996"),
    ('1997', "yr1997"),
    ('1998', "yr1998"),
    ('1999', "yr1999"),
    ('2000', "yr2000"),
    ('2001', "yr2001"),
    ('2002', "yr2002"),
    ('2003', "yr2003"),
    ('2004', "yr2004"),
    ('2005', "yr2005"),
    ('2006', "yr2006"),
    ('2007', "yr2007"),
    ('2008', "yr2008"),
    ('2009', "yr2009"),
    ('2010', "yr2010"),
    ('2011', "yr2011"),
    ('2012', "yr2012"),
    ('2013', "yr2013"),
    ('2014', "yr2014"),
    ('2015', "yr2015"),
    ('2016', "yr2016"),
    ('2017', "yr2017"),
    ('2018', "yr2018"),
) AS year_values(year1, value1)
WHERE economy='IDN')
select year1 as "year"
       , ROUND(avg(value1),1) as percentage_female_secondary_education
       , ROUND(avg(percentage_child_mortality_under_five),1) as percentage_child_mortality_under_five
from perc_female_secondary_education_country_year f
     INNER JOIN infant_mortality m ON (f.year1=m."year")
where year1 >= '1970' AND year1 <= '2018'
group by year1
order by 1