Studi Kasus SQL “Rental DVD”

Setelah kita mempelajari tips penulisan SQL yang baik dan beberapa query yang perlu dipahami untuk seorang Data Analyst dan Data Scientist, maka saatnya kita mencoba untuk mengimprove skill SQL dengan menyelesaikan studi kasus DVD Rental. Database DVD Rental ini sering dijadikan sebagai bahan ajar jika ingin mempelajari Data Analytics atau Data Science.

Anda dapat mendownload Database DVD Rental di link ini http://www.postgresqltutorial.com/postgresql-sample-database/

Sedikit penjelasan tentang Database DVD Rental, database ini memiliki

• 15 tabel,
• 1 trigger,
• 7 views
• 8 functions
• 1 domain
• 13 sequences

Untuk ER Modelnya dapat di lihat di bawah ini

Sedangkan penjelasan dari 15 tabel sebagai berikut:

• actor – menyimpan data aktor termasuk nama depan dan nama belakang.
• film – menyimpan data film seperti judul, tahun rilis, panjang, peringkat, dll.
• film_actor – menyimpan relationships antara film dan aktor.
• category – menyimpan data kategori film.
• film_category- menyimpan relationships antara film dan kategori.
• store – berisi data store termasuk staf manajer dan alamat.
• inventory – menyimpan data persediaan/inventory.
• rental – menyimpan data rental/peminjaman.
• payment – menyimpan data pembayaran pelanggan.
• staff – menyimpan data staff.
• customer – menyimpan data customer/pelanggan.
• address – menyimpan data alamat untuk staf dan pelanggan
• city – menyimpan nama kota.
• country – menyimpan nama negara.

Untuk studi kasusnya, kita akan menjawab pertanyaan-pertanyaan berikut:

1. Apa genre (sesuai permintaan/demand) film teratas dan paling jarang disewa dan berapa total penjualannya?
2. Berapa banyak pengguna yang berbeda yang telah menyewa setiap genre?
3. Berapa tarif sewa rata-rata untuk setiap genre? (urutkan dari yang tertinggi ke yang terendah)
4. Berapa banyak film sewaan yang dikembalikan terlambat, lebih awal, dan tepat waktu?
5. Di negara mana “Rent A Film” hadir dan apa basis pelanggan di setiap negara? Berapa total penjualan di setiap negara? (urutkan dari yang terbanyak ke yang paling sedikit)
6. Siapa 5 pelanggan teratas per total penjualan dan tampilkan informasi detail mengenai para pelanggan tersebut untuk diberikan hadiah atau reward.

Sebelum menyelesaikan studi kasus, sebaiknya kita memahami setiap tabel dan relasi antara tabel yang dapat Anda lihat pada ER Diagram di atas.

Yuk lanjut …

Untuk menjawab pertanyaan nomor 1, pahami apa yang ingin ditampilkan, selanjutnya identifikasi tabel apa saja yang ingin kita join. Jika kita melihat dari pertanyaannya bahwa genre film apa teratas dan paling jarang disewa dan total penjualannya, maka tabel yang akan kita lihat adalah category, film_category, film, inventory, rental, customer, payment.

Setelah mengidentifikasi tabel-tabel tersebut, coba eksplorasi tabelnya. Lihat semua variabel dan isiannya. Jika sudah, mari kita coba membuat querynya.

Oh iya.. querynya sengaja dalam bentuk picture ya, agar Anda jika ingin latihan harus mengetikkannya kembali. Practice makes perfect.. hehehe

Dari hasil query di atas, kita dapat menarik 3 kesimpulan.

• “Rent A Film” memiliki 16 genre yang tersedia.
• Kategori “Sports” adalah genre yang paling banyak disewa, dan juga memiliki total penjualan tertinggi.
• Kategori “Music” adalah genre yang paling sedikit disewa dan memiliki total penjualan terendah.

Untuk pertanyaan ke dua berapa banyak pengguna yang berbeda yang telah menyewa setiap genre?

Ingat.. Pertanyaannya berapa banyak pengguna yang berbeda, yang telah menyewa setiap genre. Artinya penggunanya uniq, jadi kita bisa menggunakan fungsi DISTINCT untuk penggunanya.

Tabel yang akan kita eksplore adalah category, film_category, film, inventory, rental, customer

Berikut querynya

Hasilnya bisa kita lihat di bawah ini:

Ada hal menarik dari sini, coba perhatikan pertanyaan pertama bahwa genre “Music” memiliki rekor sewa paling sedikit, namun genre “Music” memiliki jumlah pelanggan uniq lebih banyak dibanding genre “Travel”.

Dan tentu saja genre “Sports” yang memiliki jumlah pelanggan berbeda paling banyak.

Untuk kasus nomor 3, berapa tarif sewa rata-rata untuk setiap genre?

Tabel yang akan kita lihat adalah category, film_category, dan film

Berikut querynya:

Hasilnya bisa kita lihat di bawah ini:

Dari hasil yang kita dapatkan, kita dapat melihat bahwa rata-rata rental rate setiap genre tidak memiliki hubungan dengan sebuah kategori genre disewa.

Meskipun genre “Games” memiliki rata-rata rental rate terendah, namun menjadi top 5 dari genre yang paling banyak disewa. Dan jika dibandingkan dengan genre “Music”, genre “Music” bukanlah yang paling mahal, tapi malahan genre “Action” merupakan salah satu genre yang paling banyak disewa.

Overall, kita dapat mengatakan bahwa sebagian besar pelanggan adalah pecinta film yang berhubungan dengan “Sports”, dan tidak tertarik dengan film bergenre “Music”. 🙂

Untuk kasus nomor 4, berapa banyak film sewaan yang dikembalikan terlambat, lebih awal, dan tepat waktu?

Untuk menjawab pertanyaan ini, kita akan melihat pada tabel film, inventory, rental.

Berikut querynya.

Hasilnya

Insight yang kita dapatkan yakni status pengembalian film bisa dibilang merupakan salah satu aspek terpenting yang harus diperhatikan dalam bisnis persewaan DVD. Dari pertanyaan di atas, 48% film dikembalikan lebih awal dari tanggal jatuh tempo, sementara 41% film dikembalikan terlambat, dan 11% tiba tepat waktu.

Mungkin ada beberapa faktor mengapa ini bisa terjadi, seperti jarak pengiriman film-film ini dari toko-toko yang bisa benar-benar di luar kendali pelanggan dan sebagainya. Kita perlu menyelam lebih dalam ke data untuk mendapatkan inti dari masalah ini.

Besarnya jumlah film yang dikembalikan terlambat mungkin bisa menjadi sumber pendapatan tambahan jika dikenakan denda keterlambatan yang tinggi. Hehehe… namun memang perlu dicari tau kenapa bisa jumlah film yang kembali lewat dari jatuh tempo sangat banyak.

Untuk kasus nomor 5, di negara mana “Rent A Film” hadir dan berapa basis pelanggan di setiap negara? Berapa total penjualan di setiap negara? (urutkan dari yang terbanyak ke yang paling sedikit)

Kita akan melihat tabel country, city, address, customer, payment.

Berikut querynya

Hasilnya

Insight.

“Rent A Film” hadir di 108 negara dan India memiliki basis pelanggan tertinggi dari 60 pelanggan dan total penjualan terbesar. Afganistan memiliki total penjualan terkecil, meskipun bukan satu-satunya negara dengan basis pelanggan terkecil yaitu 1 pelanggan.

Untuk studi kasus terakhir, siapa 5 pelanggan teratas per total penjualan dan tampilkan informasi detail mengenai para pelanggan tersebut untuk diberikan hadiah atau reward.

Tabel yang akan kita lihat yakni country, city, address, customer, payment.

Berikut querynya

Hasilnya

Nah, kita bisa mendapatkan 5 pelanggan teratas dengan total penjualan tertinggi. Informasi pelanggan lengkap dengan informasi nama lengkap, alamat, email, dan lainnya. “Rent A Film” dapat memberikan mereka promo berupa discount atau semacamnya jika ada program yang disediakan.

Dari contoh kasus ini, kita menemukan banyak insight setelah mengerjakan contoh kasus ini.

Berikut 3 kesimpulan utama:

1. Perusahaan memiliki pelanggan yang menyukai Sports. Jadi untuk meningkatkan total penjualan, pihak perusahaan disarankan menyetok lebih banyak film yang berhubungan dengan olahraga dibandingkan film yang berhubungan dengan musik. Sebaiknya tingkatkan tarif sewa rata-rata film bergenre Sports karena hal tersebut bukan merupakan faktor utama dalam penyewaan bagi pelanggan. Hal ini dapat meningkatkan pendapatan total. Namun perlu dilakukan analisa lebih lanjut untuk menyimpulkan hal ini.
2. Ada potensi sumber pendapatan tambahan melalui biaya denda untuk film yang dikembalikan lebih dari waktu tempo (terlambat)
3. Rent A Film hadir di 108 negara dengan India sebagai pasar terbesar dalam hal orang dan pendapatan. Selain itu, 20% dari negara tempat mereka hadir berkontribusi pada 80% dari total basis pelanggan.

Menarik kan? Setelah ini saya akan tambahkan lebih banyak study kasus lagi. Silahkan komen dibawah jika ada studi kasus yang ingin dibahas ya…. 🙂