MEMBUAT REPORT USER REGISTER MENGGUNAKAN SQL DENGAN WITH CLAUSE DAN WINDOW FUNCTION

     Sekarang kita akan mempelajari bagaimana melakukan analisa yang umum dilakukan oleh seorang Data Analyst yaitu analisa Customer Register . Biasanya ruang lingkup Customer Register adalah, kapan Customer melakukan register, kapan apply pinjaman, lalu apakah datanya sudah terinput keseluruhan, kemudian berapa customer yang sudah pernah mendapatkan pinjaman dan berapa customer yang apply pertama nya justru di reject.

    Analisa nanti kita menggunakan eksekusi dengan SQL yaa, dengan metode With clause dan Window Function.

Pertama-tama saya akan memperlihatkan 2 buah table yaitu table "customer" dan table "loan".

Berikut adalah Table loan dimana terdapat field:

accountid, orderno, status, amount, apply_date

Lalu ini adalah table customer dimana terdapat field:

date_register, accountid dan gender.

Dari 2 table ini kita akan membuat report:

1. Total Customer yang applied perbulan dari January-2021 s/d Desember-2021.  
2. Dari total Customer kita akan melihat lagi, dari Customer yang sudah register, ada berapa yang sudah pernah apply loan.
3. Dari yang sudah apply loan ada berapa yang sudah pernah Disburse
4. Dari yang sudah apply loan ada berapa yang apply pertama kali nya itu Reject.

Dari table diatas dapat disimpulkan, table loan berisi customer-customer yang melakukan apply tersimpan disitu keseluruhan apapun statusnya. 

Kita buatkan dulu deklarasi untuk customer yang pernah apply loan. kita menggunakan with clause, deklarasi pertama adalah first_loan, dimana menggunakan window function row, bisa juga menggunakan rank. jadi kita akan menampilkan semua loan yang kita urut atau partisi kan berdasarkan "accountid" nya lalu diurutkan berdasarkan "apply_date" nya yang diurut secara ascending. setelah kita mendapatkan urutan nya nanti akan seperti ini 

karena kita hanya membutuhkan apply loan yang pertama kali saja, maka kita berikan kondisi x=1 maka akan menjadi seperti ini:

sehingga kita sudah mendapatkan unique accountid yang sudah pernah melakukan apply_loan.

Setelah sudah membuat table yang pernah apply loan, selanjutnya kita akan buat deklarasi untuk yang pernah disburse. 

kita buatkan deklarasi dengan nama "success_loan", sama dengan deklarasi yang "first_loan" dan source table nya pun sama berasal dari table "loan", namun kita menambahkan status Success yang artinya berhasil disburse. sehingga kita akan mendapatkan account-account yang sudah pernah mendapatkan disburse mereka yang untuk pertama kali.

Sekarang kita akan membuat yang apply pertama kali namun terkena reject.

dengan nama deklarasi "rejected_loan" sama dengan script yang deklarasi "success_loan" namun status yang kita ambil adalah Rejected.

setelah itu baru membuat query utama nya.

Pada kode diatas kita akan menampilkan datanya per-bulan masing-masing adalah total_register, ever_applied, ever_disbursed dan first_applied_rejected.

• Untuk yang month_register, kita convert dulu "date_register" dari table_customer. Karena format tanggal nya lengkap di date_register, maka kita convert menjadi bulan dan tahun saja. 
• Kemudian total_register kita pakai count dari accountid yang ada di table customer untuk mendapatkan total account yang sudah register. 
• Selanjutnya untuk yang ever_applied kita count accountid nya dari deklarasi "first_applied".
• Kemudian ever_disbursed kita count accountid nya dari table deklarasi "success_loan".
• Terakhir table first_applied_rejected kita count accountid dari table deklarasi "rejected_loan"

kita ambil universe nya yaitu table customer sehingga from nya adalah table customer yang kita inisialisasi dengan "a".

Untuk menghubungkan deklarasi table yang kita sudah buat dengan table customer maka kita gunakan LEFT JOIN yang artinya deklarasi-deklarasi table tersebut akan join datanya sesuai data yang ada di table customer. 

kita join berdasarkan dengan accountidnya, untuk deklarasi first_loan kita inisialisasi dengan "b", success_loan dengan "c" dan rejected_loan dengan "d".

Karena kita hanya menentukan periode nya selama Jan-21 s/d Dec-21 maka kita buat kondisi dengan WHERE date_register nya lebih dari sama dengan 1 Januari 2021 dan date_register nya kurang dari sama dengan 31 Desember 2021. sehingga nanti yang diperlihatkan adalah customer yang register di range periode tersebut. 

Karena count termasuk dalam menu Agregate maka kita harus grouping, dan kita akan grouping berdasarkan month_register nya dan kemudian kita atur terurut berdasarkan month_register nya dari yang terkecil.

Berikut adalah hasil outputnya

berikut adalah full script nya

Maka dari sini kita dapat melihat funnel customer dari mereka register hingga mereka disburse. Berapa banyak yang register, kemudian sudah pernah apply, sudah pernah disburse dapat kita lihat secara jelas.

Berikut apabila kita analisa secara grafi Funnel nya seperti gambar dibawah ini.

SEMOGA BERMANFAAT PEMBELAJARAN NYA!!!

CREATED BY: MARHENDY FAIZAL IMRANSYAH

FOR FINAL TEST PURWADHIKA