# Data preparations import pandas as pd industry = pd.read_csv("./industry.csv",sep=",") investment = pd.read_csv("./investment.csv",sep=",") company_2010 = pd.read_csv("./company_2010.csv",sep=",") company_2020 = pd.read_csv("./company_2020.csv",sep=",") company_2030 = pd.read_csv("./company_2030.csv",sep=",")

select company_2030.company as nama_perusahaan, company_2030.country as negara, industry.name as nama_industri from company_2030 left join industry on company_2030.industry_id = industry.id

select industry.name as nama_industri, company_2030.company as nama_perusahaan, company_2030.country as negara from industry left join company_2030 on company_2030.industry_id = industry.id order by nama_industri;

select company_2030.company as nama_perusahaan, company_2030.country as negara, industry.name as nama_industri from company_2030 right join industry on company_2030.industry_id = industry.id

select * from company_2030 inner join investment on company_2030.company_id = investment.company_code

select * from company_2010 union select * from company_2020 union select * from company_2030;

with idt as ( select id, name from industry where name != 'Fintech' ) ,all_company as ( select company_id, country, continent, industry_id from company_2010 union select company_id, country, continent, industry_id, from company_2020 union select company_id, country, continent, industry_id, from company_2030 ) ,all_invest as ( select valuation, company_code from investment ) ,combines as ( select I.name as nama_industri, C.company_id as id_company, C.continent as benua, S.valuation from idt as I inner join all_company as C on I.id = C.industry_id left join investment as S on C.company_id = S.company_code ) select benua, count(id_company) as total_company from combines where benua != 'Asia' group by benua having total_company >= 50 order by total_company desc ;