PostgreSQL merupakan sebuah sistem database yang handal dalam mengelola data yang besar untuk diakses oleh banyak user. Sistem manajemen basis data ini menggunakan bahasa query utama SQL, sama seperti MySQL. PostgreSQL dapat mengolah data dalam tabel yang mempunyai hubungan satu sama lain. Umumnya, manajemen database ini banyak digunakan untuk berbagai aplikasi dengan kebutuhan pengolahan data yang kompleks.
Pada Intensive Bootcamp Data Analysis ini diberikan latihan pengerjaan studi kasus mengenai perusahaan unicorn pada awal Bulan April 2022. Dataset yang digunakan telah dilakukan cleaning data dan dapat diakses melalui link sebagai berikut unicorn companies, unicorn dates, unicorn funding, unicorn industries.
Berdasarkan dataset yang dimiliki, terdapat 13 pertanyaan dengan ditampilkan query dan output sebagai berikut:
Pertanyaan 1 : Berdasarkan dataset unicorn companies, urutkan benua sesuai jumlah company terbanyak dan benua dengan unicorn terbanyak.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select continent,
count(distinct company_id) total_company
from unicorn_companies
group by continent
order by count(distinct company_id) desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 2: Berdasarkan dataset unicorn companies, tampilkan negara yang memiliki jumlah unicorn di atas 100.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select country,
count(distinct company_id) total_company
from unicorn_companies
group by country
HAVING count(distinct company_id) > 100
order by count(distinct company_id) desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 3: Tampilkan industri terbesar diantara company berdasarkan total fundingnya dan tampilkan rata-rata valuasinya menggunakan dataset unicorn industries dan unicorn funding.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select ui.industry,
sum(uf.funding) as total_funding,
round(avg(uf.valuation),0) as avg_valuation
from unicorn_industries ui
inner join unicorn_funding uf
on ui.company_id = uf.company_id
group by 1
order by 2 desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 4: Tampilkan jumlah company yang bergabung sebagai unicorn pada tahun 2016–2022 sesuai industri jawaban nomor 3 dengan dataset unicorn companies, unicorn industries, dan unicorn dates.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select extract(year from date_joined) as tahun,
count(distinct ui.company_id) as total_company
from unicorn_industries ui
inner join unicorn_dates ud
on ui.company_id = ud.company_id
where extract(year from date_joined) between 2016 and 2022
and ui.industry = 'Fintech'
group by tahun
order by tahun
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 5: Berdasarkan dataset unicorn companies, unicorn industries, dan unicorn funding, tampilkan:
- Negara yang memiliki company dengan valuasi terbesar dan industrinya
- Company dengan valuasi terbesar di Indonesia
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select uc.*, ui.industry, ui.valuation
from unicorn_companies uc
inner join unicorn_industries ui
on uc.company_id = ui.company_id
inner join unicorn_funding uf
on uc.company_id = uf.company_id
where country = 'Indonesia'
order by uf.valuation desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 6: Tampilkan umur company tertua ketika bergabung menjadi unicorn company berdasarkan dataset unicorn companies dan unicorn dates.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select uc.company, ud.date_joined, ud.year_founded,
extract(year from ud.date_joined) - ud.year_founded as company_age
from unicorn_companies uc
inner join unicorn_dates ud
on uc.company_id = ud.company_id
order by company_age desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 7: Tampilkan umur company tertua ketika bergabung menjadi unicorn company dan asal company yang didirikan tahun 1960 dan 2000 berdasarkan dataset unicorn companies dan unicorn dates.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select uc.company, ud.date_joined, ud.year_founded,
extract(year from ud.date_joined) - ud.year_founded as company_age
from unicorn_companies uc
inner join unicorn_dates ud
on uc.company_id = ud.company_id
and ud.year_founded between 1960 and 2000
order by company_age desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 8: Berdasarkan dataset unicorn funding, tampilkan:
- Banyaknya company yang dibiayai oleh minimal satu investor yang mengandung nama ‘venture’
- Banyaknya company yang dibiayai oleh minimal satu investor yang mengandung nama ‘venture’, ‘capital’, dan ‘partner’
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
Q1
select count(distinct company_id) as total_company
from unicorn_funding uf
where lower(select_investor) like '%venture%'
Q2
select
count(distinct case when lower(select_investors) like '%venture%'
then company_id END) as investor_venture,
count(distinct case when lower(select_investors) like '%capital%'
then company_id END) as investor_capital,
count(distinct case when lower(select_investors) like '%partner%'
then company_id END) as investor_partner
from unicorn_funding uf
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 9: Berdasarkan dataset unicorn companies dan unicorn industries, tampilkan banyaknya startup logistik yang termasuk unicorn di Asia dan Indonesia.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select
count(distinct uc.company_id) as total_asia,
count(distinct case when uc.country = 'Indonesia'
then uc.company_id END) as total_Indonesia
from unicorn_companies uc
inner join unicorn_industries ui
on uc.company_id = uc.company_id
where ui.industry = '"Supply chain, logistics, & delivery"'
and uc.continent = 'Asia'
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 10: Tampilkan data jumlah unicorn di tiap industri dan negara asal di Asia kecuali tiga negara dengan jumlah unicorn terbanyak berdasarkan dataset unicorn companies dan unicorn industries.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select
ui.industry,
uc.country,
count(distinct uc.company_id) as total_company
from unicorn_companies uc
inner join unicorn_industries ui
on uc.company_id = ui.company_id
left join (
select
uc1.country,
count(distinct uc1.company_id) as total_company
from unicorn_companies uc1
where uc1.continent = 'Asia'
group by 1
order by 2 desc
limit 3
) top_3
on uc.country = top_3.country
where uc.continent ='Asia' and top_3.country is NULL
group by 1,2
order by 1,3 desc, 2
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 11: Tampilkan industri yang tidak memiliki unicorn dari India berdasarkan dataset unicorn companies dan unicorn industries.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
select
distinct ui.industry
from unicorn_industries ui
where ui.industry not in (
select
distinct ui2.industry
from unicorn_companies uc
inner join unicorn_industries ui2
on uc.company_id = ui2.company_id
where uc.country = 'India'
)
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 12: Tampilkan tiga industri dengan unicorn terbanyak di tahun 2019–2021 dan jumlah unicorn serta rata-rata valuasinya tiap tahun.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
with top_3 as (
select
ui.industry,
count(distinct ui.company_id)
from unicorn_industries ui
inner join unicorn_dates ud
on ui.company_id = ud.company_id
where extract(year from ud.date_joined) in (2019,2020,2021)
group by 1
order by 2 desc
limit 3
),
yearly_rank as (
select
ui.industry,
extract (year from ud.date_joined) as year_joined,
count(distinct ui.company_id) as total_company,
round(avg(uf.valuation)/1000000000,2) as avg_valuation_billion
from unicorn_industries ui
inner join unicorn_dates ud
on ui.company_id = ud.company_id
inner join unicorn_funding uf
on ui.company_id = uf.company_id
group by 1,2
)
select y.*
from yearly_rank y
where y.year_joined in (2019,2020,2021)
and y.industry in (
select industry from top_3
)
order by 1,2 desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Pertanyaan 13: Tampilkan negara yang memiliki unicorn terbanyak dan persen proporsinya.
Query yang digunakan untuk menjawab pertanyaan tersebut yaitu :
with country_level as (
select
uc.country,
count(distinct uc.company_id) as total_per_country
from unicorn_companies uc
group by 1
)
select
*,
(total_per_country / sum(total_per_country) OVER ())*100 AS pct_company
from country_level
order by 2 desc
Output yang diperoleh dari query tersebut yaitu sebagai berikut:
Artikel ini merupakan bagian 1 latihan Data Analisis dari MySkill, jika ingin melihat bagian yang lainnya dapat diakses disini: