Data Analysis for E-Commerce Challenge

Photo by Morning Brew on Unsplash

Halloo kembali lagi bersama saya!!! akan membahas mengenai Data Analysis Challange dari DQLab dan masih berkutat dengan SQL pastinya. jangan bosan-bosan yaa :) yukk langsung saja mulai pembahasannya…

Data yang Digunakan

Dataset yang digunakan merupakan data dari DQLab Store yang merupakan e-commerce dimana pembeli dan penjual saling bertemu. Pengguna bisa membeli barang dari pengguna lain yang berjualan, Setiap pengguna bisa menjadi pembeli sekaligus penjual. Dataset dapat di download melalui link ini

Ada 4 tabel yang tersedia,

1. users, berisi detail data pengguna. Berisi,

• user_id : ID pengguna
• nama_user : nama pengguna
• kodepos : kodepos alamat utama dari pengguna
• email : email dari pengguna

2. products, berisi detail data dari produk yang dijual. Berisi,

• product_id : ID produk
• desc_product : nama produk
• category : kategori produk
• base_price : harga asli dari produk

3. orders, berisi transaksi pembelian dari pembeli ke penjual. Berisi,

• order_id : ID transaksi
• seller_id : ID dari pengguna yang menjual
• buyer_id : ID dari pengguna yang membeli
• kodepos : kodepos alamat pengirimian transaksi (bisa beda dengan alamat utama)
• subtotal : total harga barang sebelum diskon
• discount : diskon dari transaksi
• total : total harga barang setelah dikurangi diskon, yang dibayarkan pembeli
• created_at : tanggal transaksi
• paid_at : tanggal dibayar
• delivery_at : tanggal pengiriman

4. order_details, berisi detail barang yang dibeli saat transaksi. Berisi,

• order_detail_id : ID table ini
• order_id : ID dari transaksi
• product_id : ID dari masing-masing produk transaksi
• price : harga barang masing-masing produk
• quantity : jumlah barang yang dibeli dari masing-masing produk

Project Task and Answers

1. Berapakah total Transaksi Bulanan

disini saya hanya mengambil 3 bulan saja yaitu Januari 2020, September dan November 2019

SELECT
   count(distinct order_id) as total_orders_jan2020
FROM
   orders
WHERE
   created_at between '2020-01-01' and '2020-01-31'

hasilnya :

SELECT
   count(distinct order_id) as total_orders_jan2020
FROM
   orders
WHERE
   created_at between '2019-09-01' and '2019-09-30'

hasilnya :

SELECT
   count(distinct order_id) as total_orders_jan2020
FROM
   orders
WHERE
   created_at between '2019-09-01' and '2019-09-30'

hasilnya :

2. Pengguna yang pernah Bertransaksi

transaksi baik penjual atau pembeli

SELECT
   count(distinct seller_id) as total_sellerid,
   count(distinct buyer_id) as total_buyerid
FROM
   orders

• Menggunakan COUNT untuk menghitung transaksi keseluruhan antara seller_id dan buyer_id, jangan lupa agar tidak terjadi duplikasi data digunakan fungsi DISTINCT
• Total transaksi baik sebagai Penjual dan Pembeli sebesar 17,946

hasilnya :

3. Top Buyer All Time

Disini saya mengambil Top 5 Buyer dengan Total Transaksi terbesar

SELECT
   distinct buyer_id,
   nama_user as nama_pelanggan,
   sum(total) as total_transaksi
FROM
   orders a
   JOIN users b ON a.buyer_id = b.user_id
GROUP BY
   buyer_id, nama_user
ORDER BY
   total_transaksi desc 
LIMIT 5

• Menggunakan SUM untuk menjumlahkan transaksi yang dilakukan pembeli secara keseluruhan, dan jangan lupa DISTINCT buyer_id agar tidak terjadi duplikasi data
• Gunakan fungsi JOIN karena kita akan menggabungkan dua tabel yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom buyer_id dengan user_id pada fungsi ON
• Urutkan dengan total transaksi terbesar dengan ORDER BY DESC, dan ambil Top 5 dengan fungsi LIMIT

hasilnya :

4. Top Buyer in 2020

Disini saya mengambil Top 5 Buyer dengan Total Transaksi terbesar disertakan email

• Menggunakan SUM untuk menjumlahkan transaksi yang dilakukan pembeli secara keseluruhan
• Gunakan fungsi JOIN karena kita akan menggabungkan dua tabel order dan users yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom buyer_id dengan user_id pada fungsi ON
• Filter menggunakan WHERE dan BETWEEN agar tahun transaksi menjadi 2020, dan kolom email agar tidak kosong menggunakan IS NOT NULL
• Jangan lupa filter SUM dengan menggunakan HAVING, dan kelompokkan data menggunakan fungsi GROUP BY
• Urutkan dengan total transaksi terbesar dengan ORDER BY DESC, dan ambil Top 5 dengan fungsi LIMIT

hasilnya :

5. Top 5 Product December 2019

• Menggunakan SUM untuk menjumlahkan quantity dan harga produk secara keseluruhan
• Gunakan fungsi JOIN karena kita akan menggabungkan tiga tabel order, users, dan order_details yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom order_id dengan product_id pada fungsi USING
• Filter menggunakan WHERE pada transaksi tahun 2020 saja dan menghilangkan delivery yang kosong dengan IS NOT NULL
• Kelompokkan data berdasarkan category nya
• Urutkan dengan total quantity terbesar dengan ORDER BY DESC, dan ambil Top 5 dengan fungsi LIMIT

hasilnya :

6. Kategori Produk terlaris di 2020

SELECT
   category,
   sum(quantity) as total_quantity,
   sum(price) as total_price
FROM
   orders
   JOIN order_details USING(order_id)
   JOIN products USING(product_id)
WHERE
   created_at>='2020-01-01' AND delivery_at is not null
GROUP BY
   category 
ORDER BY
   total_quantity desc 
LIMIT 5

• Menggunakan SUM untuk menjumlahkan quantity dan harga produk secara keseluruhan
• Gunakan fungsi JOIN karena kita akan menggabungkan tiga tabel order, users, dan order_details yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom order_id dengan product_id pada fungsi USING
• Filter menggunakan WHERE pada transaksi tahun 2020 saja dan menghilangkan delivery yang kosong dengan IS NOT NULL
• Kelompokkan data berdasarkan category nya
• Urutkan dengan total quantity terbesar dengan ORDER BY DESC, dan ambil Top 5 dengan fungsi LIMIT

hasilnya :

7. Transaksi terbesar di Desember 2019

SELECT
   nama_user as nama_pembeli,
   total as nilai_transaksi, 
   created_at as tanggal_transaksi
FROM
   orders
   INNER JOIN users ON buyer_id = user_id
WHERE
   created_at BETWEEN>='2019-12-01' AND <'2020-01-01'
   AND total >= 20000000 
ORDER BY
   nilai_transaksi desc

• Gunakan fungsi JOIN karena kita akan menggabungkan dua tabel yaitu orders dan users yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom buyer_id dan user_id pada fungsi ON
• Filter menggunakan WHERE dan BETWEEN agar tahun transaksi menjadi Desember 2019 dan total minimal sebesar 20,000,000
• Urutkan dengan nila transaksi terbesar dengan ORDER BY DESC

hasilnya :

8. Transaksi per bulan di tahun 2020

SELECT
   EXTRACT(YEAR_MONTH FROM created_at) as tahun_bulan,
   count(1) as jumlah_transaksi,  
   sum(total) as total_nilai_transaksi
FROM
   orders
WHERE
   created_at>='2020-01-01' 
GROUP BY
1
ORDER BY
1

• Gunakan EXTRACT(YEAR_MONTH) untuk mengambil tahun dan bulan transaksinya saja. Lalu gunakan SUM untuk menjumlahkan total dan COUNT pada kolom 1 untuk jumlah transaksi
• Filter menggunakan WHERE pada transaksi tahun 2020 saja
• Kelompokkan data berdasarkan tahun_bulan
• Urutkan berdasarkan tahun_bulan dengan ORDER BY

hasilnya :

9. Pembeli High Value

SELECT
   nama_user as nama_pembeli,
   count(1) as jumlah_transaksi, 
   sum(total) as total_nilai_transaksi, 
   min(total) as min_nilai_transaksi
FROM
   orders JOIN users ON buyer_id = user_id 
GROUP BY
   user_id, 
   nama_user 
HAVING count(1)> 5 and min(total)> 2000000
ORDER BY total_nilai_transaksi DESC

• Menggunakan SUM dan MIN untuk menghitung total transaksi secara keseluruhan, dan COUNT untuk menghitung nama_user yang bertransaksi berapa kali
• Gunakan fungsi JOIN karena kita akan menggabungkan 2 tabel order dan users yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom buyer_id dengan user_id pada fungsi ON
• Filter menggunakan HAVING yang bertransaksi lebih dari 5 kali dan minimal pembelian lebih dari 2,000,000
• Kelompokkan data berdasarkan user_id dan nama_user nya
• Urutkan dengan total nilai transaksi terbesar dengan ORDER BY DESC

hasilnya :

10. Mencari Dropshipper

Untuk mencari tahu pengguna yang menjadi dropshipper, yakni pembeli yang membeli barang akan tetapi dikirim ke orang lain. Ciri-cirinya yakni transaksinya banyak, dengan alamat yang berbeda-beda. Jadi disini saya misalkan untuk mencari pembeli dengan 10 kali transaksi atau lebih yang alamat pengiriman transaksi selalu berbeda setiap transaksi.

SELECT
   nama_user as nama_pembeli,
   count(1) as jumlah_transaksi, 
   count(distinct orders.kodepos) as distinct_kodepos,
   sum(total) as total_nilai_transaksi,
   avg(total) as avg_nilai_transaksi
FROM
   orders JOIN users ON buyer_id = user_id
GROUP BY
   user_id,
   nama_user
HAVING 
   count(1)>=10 AND 
   count(1)=count(distinct orders.kodepos)
ORDER BY 
   jumlah_transaksi DESC

• Menggunakan COUNT, SUM, dan AVG untuk menghitung total transaksi secara keseluruhan, dan COUNT untuk menghitung kodepos pengiriman transaksi jangan lupa menggunakan DISTINCT agar tidak terjadi duplikasi data
• Gunakan fungsi JOIN karena kita akan menggabungkan 2 tabel order dan users yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom buyer_id dengan user_id pada fungsi ON
• Filter menggunakan HAVING yang bertransaksi kurang lebih dari 10 kali dan dihitung transaksi berdasarkan kodepos yang berbeda menggunakan COUNT
• Kelompokkan data berdasarkan user_id dan nama_user nya
• Urutkan dengan jumlah transaksi terbesar dengan ORDER BY DESC

hasilnya :

11. Lama Transaksi dibayarkan

Yakni untuk mengetahui trend lama waktu transaksi sejak dibuat

SELECT
   EXTRACT(YEAR_MONTH FROM created_at) as tahun_bulan,
   count(1) as jumlah_transaksi,
   avg(DATEDIFF(paid_at, created_at)) as avg_lama_dibayar,
   min(DATEDIFF(paid_at, created_at)) as min_lama_dibayar,
   max(DATEDIFF(paid_at, created_at)) as max_lama_dibayar
FROM
   orders
WHERE
   paid_at is not null
GROUP BY 1
ORDER BY 1

• Menggunakan COUNT untuk menghitung jumlah transaksi pada kolom tahun_bulan, dan menggunakan EXTRACT(YEAR_MONTH) untuk menggambil tahun dan bulan saja
• Menggunakan fungsi DATEDIFF untuk mencari selisih waktu transaksi, mulai dari transaksi dipesan (created_at) hingga dibayarkan (paid_at)
• Filter menggunakan WHERE is not null pada kolom paid_at, agar tidak muncul data yang kosong saat query dijalankan
• Kelompokkan data berdasarkan tahun dan bulan
• Urutkan berdasarkan tahun dan bulan

hasilnya :

Notes : disini saya hanya mengambil output beberapa saja, karena hasilnya hingga akhir 2020

12. Mencari Reseller Offline

Selanjutnya, akan dicari tahu jenis pengguna yang menjadi reseller offline atau punya toko offline, yakni pembeli yang sering sekali membeli barang dan seringnya dikirimkan ke alamat yang sama. Pembelian juga dengan quantity produk yang banyak. Sehingga kemungkinan barang ini akan dijual lagi. Disini saya akan membuat query untuk mencari pembeli yang punya 8 atau lebih transaksi yang alamat pengiriman transaksi sama dengan alamat pengiriman utama, dan rata-rata total quantity per transaksi lebih dari 10.

• Menggunakan COUNT, SUM, dan AVG untuk menghitung total transaksi secara keseluruhan, dan AVG untuk menghitung rata-rata quantity per transaksinya melalui subquery
• Gunakan fungsi JOIN karena kita akan menggabungkan 2 tabel order dan users yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom buyer_id dengan user_id pada fungsi ON, dan lakukan subquery pada fungsi join yang kedua dari tabel order_details untuk menghasilkan total_quantity yang dicari lalu gunakan USING untuk key point order_id
• Filter menggunakan WHERE pada kodepos dengan menyamakan key point kodepos pada tabel orders dan users. Selanjutnya gunakan filter having untuk perhitungan COUNT nama_user dan AVG total_quantity
• Kelompokkan data berdasarkan user_id dan nama_user nya
• Urutkan dengan total_nilai_transaksi terbesar dengan ORDER BY DESC.
• Ambil 5 tertinggi saja dengan fungsi LIMIT

hasilnya :

13. Mencari Pembeli sekaligus menjadi Penjual

Pada soal ini kita diperintah untuk mencari penjual yang juga pernah bertransaksi sebagai pembeli minimal 7 kali

• Pada query tersebut kita diperintahkan untuk mencari jumlah_transaksi_beli dan jumlah_transaksi_jual sehingga kita dapat membuat query baru
• Gunakan fungsi JOIN karena kita akan menggabungkan 2 tabel order dan users yang berbeda tetapi saling berhubungan, berdasarkan key point yaitu kolom buyer_id, seller_id dengan user_id pada fungsi ON, jangan lupa buyer_id untuk jumlah_transaksi_beli dan seller_id untuk jumlah_transaksi_jual
• Filter menggunakan WHERE pada jumlah_transaksi_beli yang pernah bertransaksi sebagai pembeli sebanyak minimal 7 kali
• Urutkan dengan total_nilai_transaksi terbesar dengan ORDER BY DESC.

hasilnya :

Alhamdulillah telah selesai Data Analysis Challenge dari DQLab, seru kann belajar SQL-nya. tetap semangat keep Learning and be Motivate, Thankyouuu…