<jupyter_start><jupyter_text>Find the country whose GDP has grown the most over time and plot the level of education on the x axis and the life expectancy on the y axis. Do the same thing for the country whose GDP has grown the least over time<jupyter_code>%%sql
drop view max;
create view max as
select location, max(time) as time from gdp group by location
%%sql
drop view min;
create view min as
select location, min(time) as time from gdp group by location
%%sql
drop view maxexp;
create view maxexp as
select location, avg(value) from max join gdp using(location, time) group by location order by location asc
%%sql
drop view minexp;
create view minexp as
select location, avg(value) from min join gdp using(location, time) group by location order by location asc
%%sql
drop view growth;
create view growth as
select location, (maxexp.avg-minexp.avg)/minexp.avg*100 as gro from minexp join maxexp using (location) order by gro asc<jupyter_output>Done.
Done.<jupyter_text>To find out the average<jupyter_code>%%sql
select * from growth
%%sql
create view maxlife as
select location, max(time) as time from life where location = 4 group by location
%%sql
create view bralife as
select location, time, avg(value) as value from life
where life.location = 4
group by time, location
order by time asc
%%sql
create view brasilian as
select education. location, education.time, avg(education.value) as eduscore, avg(bralife.value) as lifeexp from education
join bralife using(time)
where education.location = 4
group by education.time, education.location
order by education.time asc
import matplotlib.pyplot as plt
result = %sql select eduscore, lifeexp from brasilian
list1, list2 = zip(*result)
plt.scatter(list1,list2)
plt.show
%%sql
create view minlife as
select location, max(time) as time from life where location = 28 group by location
%%sql
create view kolife as
select location, time, avg(value) as value from life
where life.location = 28
group by time, location
order by time asc
%%sql
create view korea as
select education. location, education.time, avg(education.value) as eduscore, avg(kolife.value) as lifeexp from education
join kolife using(time)
where education.location = 28
group by education.time, education.location
order by education.time asc
result = %sql select eduscore, lifeexp from korea
list1, list2 = zip(*result)
plt.scatter(list1,list2)
plt.show<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.0686641697877653, 'fraction_numerical': 0.008322929671244279, 'mean_word_length': 5.427807486631016, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 8, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '638259', 'license': 'MIT', 'n_tokens_mistral': 725, 'n_tokens_neox': 701, 'n_words': 332, 'path': 'Jupyter Queries.ipynb', 'repo_name': 'Retroperspect/DBEX8-KFS', 'size': '2403'}