Data Analysis Portfolio part I of III

Analisis Data menggunakan SQL (Unicorn Company Datasets)

Halo teman-teman!

Pada kesempatan ini, saya akan membahas mengenai salah satu exercise yang saya selesaikan selama mengikuti Fullstack Intensive Bootcamp Data Analysis by MySkill. Di sini saya akan membahas mengenai SQL(Structured Query Language). Selama pembelajaran dan pengerjaan exercise, saya menggunakan PostgreSQL sebagai sistem manajemen basis data.

Preparation

PostgreSQL

PostgreSQL dapat diunduh melalui www.postgresql.org/download/

Pada tugas ini, saya diberikan 4 dataset mengenai perusahaan unicorn. Perusahaan unicorn merupakan perusahaan start-up privat yang memiliki valuasi di atas $1 miliar. Dataset ini berisi perusahaan yang tergolong unicorn pada awal April 2022, sehingga start-up yang sudah tidak tergolong sebagai unicorn karena alasan IPO atau merger seperti Gojek tidak masuk di dataset ini. Daftar perusahaan yang tergolong unicorn pada saat ini (November 2022) mungkin sudah berbeda.

Terdapat 4 dataset/tabel sebagai berikut:

• unicorn_companies
• unicorn_dates
• unicorn_funding
• unicorn_industries

Mula-mula, saya membuat database bernama “UNICORN” dengan 4 tabel bernama: unicorn_companies, unicorn_dates, unicorn_funding, unicorn_industries. Setelah itu, dilakukan import dataset yang disediakan ke 4 tabel tersebut. Lalu dilakukan pengecekan isi masing-masing tabel secara menyeluruh dengan menggunakan syntax:

SELECT * FROM table_name;

Berikut syntax dan output untuk masing-masing tabel:

SELECT * FROM unicorn_companies;

tabel unicorn_companies

SELECT * FROM unicorn_dates;

tabel unicorn_dates

SELECT * FROM unicorn_funding;

tabel unicorn_funding

SELECT * FROM unicorn_industries;

tabel unicorn_industries

Pembahasan Exercise Analisis Data

Di sini, saya diminta untuk mengolah data-data dengan berbagai kondisi. Berikut pertanyaan dan pembahasan-nya:

1 — Urutkan benua berdasarkan jumlah company terbanyak. Benua mana yang memiliki unicorn paling banyak?

Pertanyaan ini membutuhkan jawaban berupa nama-nama benua (continent) dengan jumlah company (uc.company_id) di masing-masing benua tersebut. Untuk selanjutnya diurutkan berdasarkan jumlah company dari yang terbesar hingga terkecil. Maka diberikan syntax:

SELECT
  uc.continent, 
  COUNT(DISTINCT uc.company_id) AS total_per_country
FROM unicorn_companies AS uc 
GROUP BY 1
ORDER BY 2 DESC

Uraian Syntax:

1. SELECT digunakan untuk memilih dan menampilkan kolom (pada pertanyaan ini: uc.continent dan COUNT(DISTINCT uc.company_id)).
2. COUNT(DISTINCT) digunakan untuk menghitung jumlah baris yang unik pada suatu kolom (Pada pertanyaan ini: uc.company_id).
3. AS digunakan untuk memberi alias kepada nama kolom/tabel (Pada pertanyaan ini: unicorn_companies dialiaskan uc dan COUNT(DISTINCT uc.company_id) dialiaskan total_per_country).
4. FROM digunakan untuk menyatakan dari manakah kolom diambil (Pada pertanyaan ini kolom diambil dari unicorn_companies).
5. GROUP BY digunakan untuk mengelompokkan data berdasarkan satu atau beberapa kolom (Pada pertanyaan ini, data dikelompokkan berdasarkan uc.continent (kolom ke-1 pada SELECT)).
6. ORDER BY digunakan untuk mengurutkan data berdasarkan satu atau beberapa kolom dengan default=ascending (Pada pertanyaan ini data diurutkan berdasarkan total_per_country secara descending).

Sehingga dihasilkan output berupa tabel sebagai berikut:

Kesimpulan: Benua Amerika Utara memiliki jumlah unicorn paling banyak.

2 — Negara apa saja yang memiliki jumlah unicorn di atas 100? (Tampilkan jumlahnya)

Pertanyaan di atas membutuhkan jawaban berupa jumlah company_id yang dikelompokkan berdasarkan negara (country). Lalu dilakukan filter data dengan syarat jumlah company_id >100. Berikut syntaxnya:

SELECT 
 uc.country, 
 COUNT(DISTINCT uc.company_id) AS total_per_country 
FROM unicorn_companies AS uc 
GROUP BY 1 
HAVING COUNT(DISTINCT uc.company_id) > 100 

Uraian Syntax:

• Memunculkan 2 kolom, yaitu country dan total_per_country yang diambil dari tabel UC
• Data dikelompokkan berdasarkan kolom ke-1 yaitu country
• digunakan command HAVING untuk memfilter suatu fungsi agregasi (Pada pertanyaan ini, dilakukan filter data pada COUNT(DISTINCT uc.company_id) agar hanya memunculkan total_per_country > 100 saja)

Sehingga dihasilkan output sebagai berikut:

Kesimpulan: Negara yang memiliki unicorn di atas 100 adalah China dan Amerika Serikat (United States).

3 — Industri apa yang paling besar di antara unicorn company berdasarkan total fundingnya? Berapa rata-rata valuasinya?

Pada pertanyaan ini, digunakan 2 tabel, yaitu unicorn_industries untuk mengambil kolom industry dan unicorn_funding untuk mengambil total funding dan rata-rata valuasi.

Berikut syntax yang digunakan:

SELECT 
 ui.industry, 
 SUM(uf.funding) AS total_funding, 
 ROUND(AVG(uf.valuation)) AS avg_valuation 
FROM unicorn_industries AS ui INNER JOIN unicorn_funding AS uf 
ON ui.company_id = uf.company_id 
GROUP BY 1 
ORDER BY 2 DESC

Uraian Syntax:

• Memunculkan 3 kolom, yaitu industry, total_funding, avg_valuation
• total_funding merupakan kolom yang berisi penjumlahan nilai dari kolom funding. Penjumlahan dari suatu nilai pada kolom menggunakan command SUM().
• avg_valuation merupakan kolom yang berisi rata-rata dari kolom valuation yang telah dibulatkan. Perhitungan rata-rata dari suatu nilai pada kolom menggunakan command AVG(), sedangkan pembulatan nilai menggunakan command ROUND().
• INNER JOIN digunakan untuk mengambil baris dari dua tabel atau lebih dengan syarat pada baris tersebut terdapat kecocokan key di kedua tabel. Syntax INNER JOIN:

SELECT column_name
FROM table1
INNER JOIN table2
ON table1.column_key = table2.column_key

• Pada pertanyaan ini, dilakukan INNER JOIN terhadap tabel unicorn_industries dan unicorn_funding dengan key company_id.
• Data dikelompokkan berdasarkan industry
• Data diurutkan berdasarkan total_funding secara descending (besar ke kecil).

Sehingga dihasilkan output sebagai berikut:

Kesimpulan: Industri terbesar berdasarkan total fundingnya adalah Industri Fintech dengan rata-rata valuasi $ 3.937.500.000,-

4 — Pada industri Fintech, berapakah jumlah company yang bergabung sebagai unicorn di tiap tahunnya di rentang tahun 2016–2022?

Pada pertanyaan ini, kita perlu menghasilkan output yang berisi tahun 2016–2022 dengan rincian jumlah company pada industri fintech.

Berikut syntax yang digunakan:

SELECT
 EXTRACT(YEAR FROM ud.date_joined) AS year_joined,
 COUNT(DISTINCT uc.company_id) AS total_company 
FROM unicorn_companies AS uc 
INNER JOIN unicorn_industries AS ui ON uc.company_id = ui.company_id 
INNER JOIN unicorn_dates AS ud ON uc.company_id = ud.company_id 

WHERE industry = 'Fintech' 
AND EXTRACT(YEAR FROM ud.date_joined) BETWEEN 2016 AND 2022 
GROUP BY 1 
ORDER BY 1 DESC

Uraian Syntax:

• Memunculkan kolom year_joined dan total_company.
• EXTRACT(YEAR) digunakan untuk mengekstrak tahun dari kolom yang bernilai tanggal. Pada pertanyaan ini, dilakukan ekstrak tahun pada kolom date_joined untuk menghasilkan kolom year_joined.
• Kolom-kolom yang digunakan diambil dari tabel UC yang di-INNER JOIN-kan dengan UI dan UD dengan key column_id.
• WHERE digunakan untuk memfilter data berdasarkan kondisi yang diinginkan. Pada pertanyaan ini, dilakukan filter terhadap industry agar memunculkan hanya “Fintech” dan terhadap tahun agar terbatasi hanya dalam rentang 2016–2022.
• Data dikelompokkan berdasarkan year_joined.
• Data diurutkan berdasarkan year_joined secara descending.

Sehingga dihasilkan output sebagai berikut:

Kesimpulan dapat dilihat pada tabel di atas.

5 — Tampilkan data detail company (nama company, kota asal, negara dan benua asal) beserta industri dan valuasinya. Dari negara mana company dengan valuasi terbesar berasal dan apa industrinya?

SELECT 
 uc.*, 
 ui.industry, 
 uf.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 
ORDER BY uf.valuation DESC

Uraian Syntax:

• Memunculkan semua kolom dari tabel UC, serta kolom industry dan valuation.
• Data diambil dari tabel UC yang di-INNER JOIN-kan dengan tabel UI dan UF.
• Data diurutkan berdasarkan valuation secara descending.

Sehingga output yang dihasilkan adalah sebagai berikut:

Kesimpulan: company dengan valuasi terbesar adalah Bytedance yang berasal dari negara China. Bytedance termasuk dalam industri Artificial Intelligence.

Bagaimana dengan Indonesia? Company apa yang memiliki valuasi paling besar di Indonesia?

Kita hanya perlu menambahkan filter menggunakan WHERE pada syntax sebelumnya.

SELECT 
 uc.*, 
 ui.industry, 
 uf.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

Dihasilkan output sebagai berikut:

Kesimpulan: Company dengan valuasi terbesar di Indonesia adalah J&T Express.

6 — Berapa umur company tertua ketika company tersebut bergabung menjadi unicorn company? Dari negara mana company tersebut berasal?

SELECT 
 uc.company,
 uc.country,
 EXTRACT(YEAR FROM ud.date_joined) AS year_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

Uraian Syntax:

• Memunculkan kolom company, country, year_joined, year_founded, dan company_age.
• Kolom year_joined dibentuk dari ekstrak tahun date_joined
• Kolom company_age dibentuk dari pengurangan ekstrak tahun date_joined dan year_founded.
• Data diambil dari tabel UC yang di-INNER JOIN-kan dengan tabel UD.
• Data diurutkan berdasarkan company_age secara descending.

Sehingga dihasilkan output sebagai berikut:

Kesimpulan: Umur Otto Bock Healthcare saat bergabung menjadi unicorn adalah 98 tahun. Perusahaan ini berasal dari Jerman.

7 — Untuk company yang didirikan tahun antara tahun 1960 dan 2000 (batas atas dan bawah masuk ke dalam rentang), berapa umur company tertua ketika company tersebut bergabung menjadi unicorn company (date_joined)? Dari negara mana company tersebut berasal?

SELECT 
 uc.company,
 uc.country,
 EXTRACT(YEAR FROM ud.date_joined) AS year_joined,
 ud.year_founded,
 EXTRACT(YEAR FROM ud.date_joined) - ud.year_founded AS company_age 
FROM unicorn_companies AS uc 
INNER JOIN unicorn_dates AS ud ON uc.company_id = ud.company_id 
WHERE ud.year_founded BETWEEN 1960 AND 2000 
ORDER BY company_age DESC

Uraian Syntax:

• Memunculkan kolom company, country, year_joined, year_founded, dan company_age.
• Kolom year_joined dibentuk dari ekstrak tahun date_joined.
• Kolom company_age dibentuk dari pengurangan ekstrak tahun date_joined dan year_founded.
• Data diambil dari tabel UC yang di-INNER JOIN-kan dengan tabel UD.
• year_founded difilter dengan kondisi nilainya di antara 1960 dan 2000.
• Data diurutkan berdasarkan company_age secara descending.

Sehingga dihasilkan output sebagai berikut:

Kesimpulan: company dengan umur tertua saat bergabung menjadi unicorn adalah Five Star Bussiness Finance dari India dan Promasidor Holdings dari Afrika Selatan.

8.1 — Ada berapa company yang dibiayai oleh minimal satu investor yang mengandung nama ‘venture’?

SELECT 
 COUNT(DISTINCT company_id) AS total_company 
FROM unicorn_funding AS uf 
WHERE LOWER(select_investors) LIKE '%venture%'

Uraian Syntax:

• Memunculkan kolom total_company yang dibentuk dengan menghitung jumlah company_id yang unik.
• Kolom diambil dari tabel UF.
• LOWER() digunakan untuk mengubah karakter string menjadi berhuruf kecil. Pada pertanyaan ini, digunakan LOWER(select_investors) untuk mengubah karakter select_investors menjadi berhuruf kecil.
• LIKE() digunakan untuk menampilkan data berdasarkan sebuah pattern (pola) yang sesuai dengan kebutuhan.
• Melakukan filter terhadap LOWER(select_investors) agar memunculkan hanya baris dengan nilai yang mengandung ‘ventures’

Sehingga dihasilkan ouput sebagai berikut:

Kesimpulan: Terdapat 603 company yang dibiayai oleh minimal satu investor yang mengandung nama ‘venture’.

8.2 — Ada berapa company yang dibiayai oleh minimal satu investor yang mengandung nama: Venture, Capital, Partner

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 AS uf

Uraian Syntax:

• Memunculkan kolom investor_venture, investor_capital, dan investor_partner dari tabel UF
• CASE digunakan untuk melewati kondisi dan mengembalikan nilai saat kondisi pertama terpenuhi (seperti pernyataan IF-THEN-ELSE). Pada pertanyaan ini untuk kolom investor_venture, diterapkan jika kondisi LOWER(select_investors) mengandung ‘venture’ terpenuhi, maka company_id unik-nya akan dihitung. Hal ini juga sama untuk untuk investor_capital dan investor_partner.

Sehingga dihasilkan output sebagai berikut:

Kesimpulan dapat dilihat pada tabel di atas.

9 — Di Indonesia terdapat banyak startup yang bergerak di bidang layanan logistik. Ada berapa startup logistik yang termasuk unicorn di Asia? Berapa banyak startup logistik yang termasuk unicorn di Indonesia?

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 AS uc INNER JOIN unicorn_industries AS ui 
ON uc.company_id = ui.company_id 
WHERE LOWER(ui.industry) LIKE '%logistics%' 
AND uc.continent = 'Asia'

Uraian Syntax:

• Memunculkan kolom total_asia dan total_indonesia.
• Kolom total_asia dibentuk dengan menghitung company_id yang unik.
• Kolom total_indonesia dibentuk dengan menghitung company_id yang unik dengan kondisi country = ‘Indonesia’.
• Kolom diambil dari tabel UC dan UI dengan kolom key: company_id.
• Dilakukan filter terhadap LOWER(ui.industry) untuk hanya memunculkan baris yang mengandung kata “logistics” dan terhadap continent untuk memunculkan hanya “Asia”

Sehingga dihasilkan output sebagai berikut:

Kesimpulan: Terdapat 26 start-up logistik unicorn di Asia dan 1 start-up logistik unicorn di Indonesia.

10 — Di Asia terdapat tiga negara dengan jumlah unicorn terbanyak. Tampilkan data jumlah unicorn di tiap industri dan negara asal di Asia, terkecuali tiga negara tersebut. Urutkan berdasarkan industri, jumlah company (menurun), dan negara asal.

Pada pertanyaan ini, perlu dibuatkan CTE(Common Table Expression)/Klausa WITH agar lebih efisien. CTE merupakan hasil sementara dari suatu set yang nantinya bisa direferensikan di SELECT, INSERT, UPDATE, dan DELETE. Untuk lebih jelasnya dapat dilihat dari syntax berikut ini:

WITH temp_table AS (cte_query)
# selanjutnya bisa dimasukkan command-command 
#  yang kita inginkan dengan menyertakan temp_table

Syntax:

WITH top_3 AS 
 (SELECT 
   uc.country, 
   COUNT(DISTINCT uc.company_id) AS total_company 
  FROM unicorn_companies uc 
  WHERE uc.continent = 'Asia' 
  GROUP BY 1 
  ORDER BY 2 DESC 
  LIMIT 3)
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 
WHERE 
 uc.continent = 'Asia' AND
 uc.country NOT IN ( SELECT DISTINCT country FROM top_3 ) 
GROUP BY 1,2 
ORDER BY 1,3 DESC,2

Uraian Syntax:

• Membuat CTE top_3 dengan klausa WITH.
• Memunculkan kolom industry, country, dan total_company.
• Kolom total_company dibentuk dengan menghitung company_id yang unik.
• Data diambil dari tabel UC yang di-INNER JOIN-kan dengan tabel UI.
• Dilakukan filter terhadap continent untuk memunculkan hanya “Asia” dan terhadap country untuk mengecualikan negara dalam top_3.
• Data dikelompokkan berdasarkan industry dan country.
• Data diurutkan berdasarkan industry secara ascending, lalu total_company secara descending, dan country secara ascending.

Sehingga dihasilkan output:

Kesimpulan dapat dilihat pada tabel di atas.

11 — Amerika Serikat, China, dan India adalah tiga negara dengan jumlah unicorn paling banyak. Apakah ada industri yang tidak memiliki unicorn yang berasal dari India? Apa saja?

WITH industry_india as (
 SELECT DISTINCT
  industry 
 FROM unicorn_industries
 INNER JOIN unicorn_companies ON unicorn_industries.company_id = unicorn_companies.company_id
 WHERE country = 'India')
 
SELECT DISTINCT
 industry 
FROM unicorn_industries
WHERE industry NOT IN (SELECT industry FROM industry_india)

Uraian Syntax:

• Membuat CTE industry_india dengan klausa WITH.
• Memunculkan kolom industry dari tabel unicorn_industries.
• Dilakukan filter terhadap industry untuk tidak memunculkan industry di CTE industry_india.

Sehingga dihasilkan output sebagai berikut:

Output nomor 11

Kesimpulan: Industri di India yang tidak memiliki unicorn adalah Consumer & Retail, Hardware, Artificial Intelligence, dan Cybersecurity.

12 — Cari tiga industri yang memiliki paling banyak unicorn di tahun 2019–2021 dan tampilkan jumlah unicorn serta rata-rata valuasinya (dalam milliar) di tiap tahun.

WITH top_3 AS 
  (SELECT 
   ui.industry, 
   COUNT(DISTINCT ui.company_id) 
   FROM unicorn_industries AS ui 
   INNER JOIN unicorn_dates AS 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 AS y 
INNER JOIN top_3 ON y.industry = top_3.industry 
WHERE y.year_joined IN (2019,2020,2021) 
ORDER BY 1,2 DESC

Uraian Syntax:

• Menciptakan CTE top_3 dan yearly_rank.
• Memunculkan semua kolom pada yearly_rank.
• Data diambil dari tabel yearly_rank di-INNER JOIN-kan dengan tabel top_3 dengan key: industry
• Data diurutkan berdasarkan industry secara ascending lalu berdasarkan year_joined secara descending.

Sehingga dihasilkan output sebagai berikut

Kesimpulan dapat dilihat pada tabel di atas.

13 — Negara mana yang memiliki unicorn paling banyak (seperti pertanyaan nomor 1) dan berapa persen proporsinya?

WITH country_level AS 
 (SELECT
   uc.country,
   COUNT(DISTINCT uc.company_id) AS total_per_country
  FROM unicorn_companies AS uc 
  GROUP BY 1 ) 

SELECT 
 *,
 (total_per_country / SUM(total_per_country) OVER())*100 AS pct_company 
FROM country_level 
ORDER BY 2 DESC

Uraian Syntax:

• Menciptakan CTE country_level.
• Memunculkan semua kolom serta pct_company dari CTE country_level.
• Data diurutkan berdasarkan pct_company secara descending.

Sehingga diperoleh output sebagai berikut:

Kesimpulan: Negara dengan unicorn terbanyak adalah Amerika Serikat dengan persentase 52,33%

Sekian pembahasan Analisis Data menggunakan SQL, semoga bermanfaat!

Baca juga:

• Part II: Python
• Part III: Google Data Studio