Final Portfolio Project SQL for Data Analysis
Halo teman-teman
Pada artikel ini, saya akan membagikan Portfolio Project yang didapatkan ketika mengikuti Intensive Bootcamp Data Analyst dari Myskil.id selama satu bulan ini. Sebelumnya, background saya adalah silvikultur atau budidaya hutan. Jelas bahwa tidak ada hubungannya dengan yang namanya bahasa pemrogaman, koding dan yang berkaitan lainnya. Namun, di suatu saat saya sangat tertarik untuk mempelajari bidang data analyst ini dan ingin mendalami serta bercita-cita untuk bekerja di bidang data.
Terima kasih juga kepada Myskill, para mentor dan teman-teman yang telah membantu saya dalam memahami SQL ini.
Baik, kita lanjutkan ke projectnya. Kali ini, kita akan belajar tentang SQL mengenai pengertian serta penggunaannya. SQL atau Structured Query Language merupakan bahasa pemrograman untuk menyimpan dan memproses informasi dalam basis data relasional. SQL digunakan untuk berkomunikasi tentang database ketika kita ingin mendapatkan data yang tersimpan dalam database.
Pada project ini, kita menggunakan dataset “Tokohapeku” yang di dalamnya terdapat tabel order_detail, sku_detail, customer_detail dan payment_detail. Command SQL lengkap ada pada link berikut.Adapun skema pada dataset ini dapat dilihat dari ERD berikut:
Untuk project ini, saya menggunakan sqlite pada spesifik PostgreSQL. Sebelum kita mengerjakan projectnya, mari kita input keempat tabel tadi terlebih dahulu. Untuk query dalam memasukkan data dapat diakses disini. Setelah memasukkan keempat tabel, kita dapat mempelajari dan melihat isi tabel menggunakan query berikut:
Untuk hasil query dari tabel di atas sebagai berikut:
Adapun rincian dari isi tiap tabel sebagai berikut:
Setelah mengecek semua data, saatnya kita menyelesaikan persoalan yang diberikan.
1. Selama transaksi yang terjadi selama 2021, pada bulan apa total nilai transaksi (after_discount) paling besar? Gunakan is_valid = 1 untuk memfilter data transaksi.
Pada permintaan ini, kita diminta untuk menampilkan total nilai transaksi yang terjadi selama tahun 2021 dan pada bulan berapa transaksi terjadi paling besar. Ada perintah juga kita perlu menggunakan is_valid = 1 yang artinya pelanggan sudah melakukan pembayaran. Soal ini, dapat kita jawab dengan query berikut:
Berikut penjelasan query di atas:
‘SELECT’ digunakan untuk memilih data mana yang akan ditampilkan pada hasil query. Kita menambahkan ‘EXTRACT’ untuk mengambil spesifik dari data yang diinginkan, dalam hal ini kita akan menampilkan bulan saja. Kemudian, ‘AS’ digunakan untuk mempermudah penamaan kolom tabel yang diinginkan.
‘SUM’ berfungsi untuk menjumlahkan total transaksi dari kolom aftar_discount.
‘FROM’ untuk mengambil dataset berdasarkan tabel dari data yang diolah.
‘WHERE’ digunakan untuk memfilter data yang dicari, dalam hal ini kita menginginkan data selama tahun 2021 saja, maka kita menggunakan ‘EXTRACT(YEAR)’ dari kolom order_date = 2021 serta memastikan pelanggan telah melakukan pembayaran, sehingga kita memasukkan perintah ‘AND’ yang menandakan bahwa kedua kondisi (tahun 2021 dan pelanggan telah membayar) harus terpenuhi (is_valid = 1). ‘GROUP BY’ digunakan untuk mengelompokkan data berdasarkan bulan dari kolom order_date. ‘ORDER BY’ digunakan untuk mengurutkan data yang memiliki nilai yaitu pada kolom total_transaction. Kemudian, kita menambahkan perintah ‘DESC’ atau descending untuk mengurutkan dari yang terbesar.
‘LIMIT’ untuk membatasi jumlah data yang muncul hanya satu saja yaitu bulan dan transaksi terbesar pada tahun 2021.
Jika kita me-run query tadi, kita mendapatkan hasil bahwa bulan delapan (Agustus 2021) memiliki total transaksi terbesar.
2. Selama transaksi pada tahun 2022, kategori apa yang menghasilkan nilai transaksi paling besar? Gunakan is_valid = 1 untuk memfilter data transaksi.
Pada permintaan ini, kita diminta untuk menampilkan kategori dengan transaksi total terbanyak pada tahun 2022. Dalam permintaan ini, kategori berada di tabel sku_detail, sedangkan transaksi berada di tabel order_detail, maka kita perlu menggabungkan kedua tabel tersebut. Kita dapat menuliskan query berikut:
Berikut penjelasan query di atas:
‘SELECT’ untuk menampilkan kolom yang diinginkan yaitu tahun 2022, kemudian total transaksi dan kategorinya. ‘FROM’ untuk mengambil dataset yang digunakan yaitu sku_detail. Karena kita akan menggabungkan dua tabel, untuk mempermudahnya kita menambahkan alias ‘s’.
Selanjutnya, gabungkan tabel dengan perintah ‘JOIN’ karena kita akan melihat kategori pada tahun 2022. Untuk itu kita gabungkan dengan tabel order_detail dan dialiaskan menjadi ‘o’. Untuk menggabungkan tabel tersebut, kita membutuhkan primary key dan foreign key yang berelasi yaitu ‘o.sku_id’ dan ‘s.id’.
Pada perintah ‘WHERE’, kita perlu mendetailkan data yang dicari yaitu data pelanggan yang sudah membayar pada tahun 2022. Selanjutnya, kita perlu mengelompokkan (‘GROUP BY’) berdasarkan tahun dan kategori sebelumnya akhirnya kita mengurutkan (’ORDER BY’) dengan total_transaction dari terbesar (’DESC’) dan diberi LIMIT 1.
Hasil query menunjukkan bahwa pada tahun 2022 total transaksi terbesar terdapat pada kategori ‘Mobiles & Tablets’.
3. Bandingkan nilai transaksi dari masing-masing kategori pada tahun 2021 dengan 2022. Sebutkan kategori apa saja yang mengalami peningkatan dan kategori apa yang mengalami penurunan nilai transaksi dari tahun 2021 ke 2022. Gunakan is_valid = 1 untuk memfilter data transaksi.
Pada soal ini, kita diminta untuk menampilkan data total transaksi tiap kategori pada tahun 2021 dan 2022. Kemudian, akan dibandingkan apakah pada tahun 2021 dan 2022 terjadi kenaikan atau penurunan total transaksi pada tiap kategorinya. Untuk itu, kita perlu menggunakan sub-query. Sub-query merupakan query yang dilakukan pada query sebelumnya atau query dalam query. Fungsinya untuk meringkas data yang akan ditampilkann dari hasil data sebelumnya.
Untuk menyelesaikannya, kita akan menggunakan sub-query. Sub-query yaitu perintah yang di dalamnya terdapat query lagi.
Query pertama tersebut, kita ingin menampilkan kategori dari tabel sku_detail. Kemudian menggunakan ‘CASE WHEN’ untuk menghitung total transaksi jika diambil dari tahun 2021 dan tahun 2022. ‘FROM’ dari tabel mana kita mengambil datanya. Lalu kita gabungkan menggunakan ‘LEFT JOIN’ karena ingin mendapatkan seluruh data dari keuda tabel dalam hal ini order_detail. ‘ON’ digunakan untuk relasi antar tabel. ‘WHERE’ yaitu filter data yang ingin diambil, kemudian ‘GROUP BY’ untuk mengelompokkan hasil berdasarkan kategori. ‘HAVING’ digunakan untuk memilih hanya kategori-kategori yang total nilai transaksinya tidak sama antara tahun 2021 dan tahun 2022 karena kita menambahkan tanda ‘! =’ yang berarti kita yakin bahwa ada perubahan data dari tahun 2021 ke 2022. Lalu kita urutkan data menggunakan ‘ORDER BY’ berdasarkan kategorinya.
Gambar tersebut merupakan hasil query pertama yang menampilkan perbandingan antara total transaksi pada tahun 2021 dan 2022. Terlihat bahwa ada perubahan transkasi pada kedua tahun tersebut. Namun, kita belum mengetahui kategori mana saja yang mengalami peningkatan maupun penurunan nilai transaksi. Untuk itu, kita menggunakan sub-query untuk menambahkan kolom baru berupa keterangan pada performa transaksi tiap kategori selama 2 tahun berjalan.
Untuk sub-querynya dapat kita masukkan seperti pada query dalam kotak merah. Terlihat, kita menambahkan ‘SELECT’ ‘category’, ‘total_transaction_2021’ dan ‘total_transaction_2022’ dari query sebelumnya dan mengikutkan ‘CASE WHEN’ dengan perintah jika total transaksi 2022 > 2021 makan termasuk ‘UP’ dan selain itu maka ‘DOWN’ .
Maka, didapatkan hasil query di atas yang menunjukkan bahwa kategori Books dan Other menalami penurunan transaksi “DOWN” dari tahun 2021 ke 2022. Kemudian, untuk transaksi yang mengalami peningkatan ialah Appliances, Beauty & Grooming, Computing, Entertainment, Healt & Sports, Home & Living, Kids & Baby, Men Fashion, Mobiles & Tablets, School & Education, Soghaat, Superstore dan Women Fashion.
4. Tampilkan top 5 metode pembayaran yang paling populer digunakan selama 2022 (berdasarkan total unique order). Gunakan is_valid = 1 untuk memfilter data transaksi.
Pada soal tersebut, kita diminta untuk menampilkan 5 metode pembayaran terpopuler pada tahun 2022 berdasarkan total unique ordernya. Untuk itu, kita dapat tuliskan query seperti berikut :
Query tersebut yaitu kita memilih dahulu tahun berapa yang akan ditampilkan yaitu 2022, dengan ‘SELECT’ kemudian ‘EXTRACT(YEAR FROM) karena akan mengambil tahun saja, kemudian agar ordernya unik, makan perlu menggunakan ‘DISTINCT’ pada ‘COUNT’. Karena ini akan menggabungkan dua tabel. beri tambahan alias tabel pada kolom yang dituju.
‘FROM’ yaitu dari tabel mana data diambil, ‘JOIN’ itu akan digabungkan ke tabel apa dan ‘ON’ sebagai kolom yang berelasi antar dua tabel. ‘WHERE’ untuk memfilter data yang dicari. ‘GROUP BY’ untuk mengelompokkan hasil query, ‘ORDER BY’ untuk mengurutkan dari order yang paling banyak dilakukan (DESC) dan diakhiri LIMIT 5 sebagai jumlah baris data yang akan ditampilkan.
Hasil query tersebut menginformasikan bahwa metode pembayaran terpopuler selama tahun 2022 ialah COD, Payaxis, Customercredit, Easypay dan Jazzwallet.
5. Urutkan dari ke-5 produk ini berdasarkan nilai transaksinya (Samsung, Apple, Sony, Huawei, Lenovo). Gunakan is_valid = 1 untuk memfilter data transaksi.
Pada soal ini kita akan menampilkan tabel dengan kolom total transaksi dan merk barang, kemudian diurutkan dari yang paling tinggi nilai transaksinya. Kali ini, kita akan menggunakan CTE(Common Table Expression) yaitu membuat tabel virtual dari hasil query sebelumnya. Pada query di bawah ini, kita akan membuat tabel virtual “mobile” yang didalamnya terdapat query berisikan CASE WHEN. Mengapa CASE WHEN? Karena setelah dilihat pada tabel sku_detail, ternyata nama produk yang akan dicari tidak menggunakan kata tunggal, melainkan berada pada kalimat. Sehingga, kita harus mengambil sebagian kata dari kalimat tersebut untuk diubah menjadi permisalan agar mempermudah dalam pengelompokkannya.
Kemudian, dalam klausa CASE WHEN tersebut, kita juga menambahkan klausa LOWER untuk menyamakan ukuran pada kalimat yang dicari. Kita masukkan CASE WHEN sesuai perintah soal, lalu aliaskan semua CASE WHEN tersebut dalam kolom bernama “product_name” dan tambahkan kolom “total_sales” untuk menjumlahkan total transaksi pada setiap kategori produk yang dipilih (Samsung, Apple, Sony, Huawei dan Lenovo).
Karena kolom product_name dan after_discount berada di tabel sku_detail dan order_detail, maka kita perlu men”JOIN”kan keduanya dan mengurutkannya berdasarkan product_name.
Terakhir, CTE tidak bisa bekerja, jika kita belum memilih kolom mana yang aakan ditampilkan pada tabel virtual. Sehingga kita perlu memilih semua data pada tabel “mobile”, dimana product_name tidak boleh kosong (IS NOT NULL) kemudian diurutkan berdasarkan “total_sales” dari yang paling tinggi (DESC).
Hasil query menunjukkan bahwa Samsung menduduki peringkat pertama sebagai produk dengan nilai transaksi tertinggi.
Demikian, bagaimana proses saya dalam pengerjaan soal SQL ini. Mungkin belum sempurna, namun pada tahap saya merasa sangat senang karena saya menemukan passion yang ternyata ada dalam bidang data dan hari demi hari, saya memanfaatkan waktu untuk terus belajar upgrade diri baik dari kompetensi, hardskill maupun softskill yang dibutuhkan sebagai Data Analyst.
Selamat bertemu di tulisan saya selanjutnya, semoga dapat membantumu dalam belajar.
Terima kasih.
