MySkill Data Analysis Part 1 PostgreSQL

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:

1. Negara yang memiliki company dengan valuasi terbesar dan industrinya
2. 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:

Output pertanyaan nomor 1

Output pertanyaan nomor 2

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:

1. Banyaknya company yang dibiayai oleh minimal satu investor yang mengandung nama ‘venture’
2. 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:

Output pertanyaan nomor 1

Output pertanyaan nomor 2

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:

Part 2 : Python through Google Colab

Part 3 : Data Visualization using Google Data Studio