# 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
;