Analisis Data Penjualan E-Commerce dengan PostgreSQL

source: The Beginner’s Guide to SQL for Data Analysis | Inzata Analytics

Pemahaman pengoprasian Excel/Spredsheet memang salah satu skill wajib yang perlu dikuasai untuk melakukan pengolahan atau perhitungan data. Namun bagaimana jika data yang dikelola memiliki lebih dari jutaan bahkan miliaran data? Alih-alih sebagai sistem manajemen database, seperti Access, Excel dioptimalkan untuk melakukan analisis dan perhitungan data. Sayangnya, excel memiliki keterbatasan dalam mengolah data dengan maksimal jumlah baris = 1.048.576 saja. Sedangkan data yang dikumpulkan atau dihasilkan dalam bisnis dan industri dapat mencapai lebih dari jutaan atau miliaran perhari.

Dengan memanfaatkan teknologi database, dapat mempermudah untuk melakukan pengumpulan data yang tersimpan secara sistematis dan terorganisir dengan jumlah data yang besar. Untuk bisa mengelola dan “berkomunikasi” ke sistem database tersebut, dibutuhkan suatu bahasa khusus, salah satunya adalah SQL.

Apa Itu SQL?

source: Introducing to SQL for Data Engineer

Dikutip dari Dataquest, SQL atau Standard Query Language adalah bahasa pemrograman yang digunakan dalam mengakses, mengubah, dan memanipulasi data yang berbasis relasional.

Keberadaan SQL dalam mengolah sebuah database memberikan fungsi tersendiri. SQL juga bisa digunakan untuk menghapus sebuah data pada database. Setidaknya ada tiga jenis perintah dasar dalam SQL, yaitu:

1. DDL (Data Definition Language) yang digunakan untuk mendefinisikan data seperti membuat tabel database baru, mengubah dataset, dan menghapus data, seperti CREATE, ALTER, RENAME, DROP.
2. DML (Data Manipulation Language) yang digunakan untuk memanipulasi data, seperti INSERT, SELECT, UPDATE, DELETE.
3. DCL (Data Control Language) yang digunakan khususnya untuk mengatur hak apa saja yang dimiliki oleh pengguna, baik itu hak terhadap sebuah database ataupun pada tabel, seperti GRANT, REVOKE.

Penggunaan SQL ini punya kelebihan jika dibandingkan dengan Excel maupun Spreadsheet, seperti:

• Database SQL bisa menangani data dengan jumlah yang cukup besar.
• Database SQL punya struktur yang lebih teratur guna melindungi integeritas data.
• Kecil kemungkinan berakhir pada suatu inkonsistensi atau kesalahan data.

Studi Kasus Data Analisis menggunakan SQL

Ada banyak aplikasi atau platform yang dapat digunakan untuk mengolah database. Untuk latihan mengolah data menggunakan SQL juga dapat dilakukan secara online tanpa harus menginstal aplikasi salah satunya yaitu melalui sqliteonline.com. Pada studi kasus ini saya menggunakan aplikasi PostgreSQL untuk melakukan query dari data yang akan diolah.

Latihan soal ini merupakan Final Project yang saya selesaikan pada bootcamp Data Analysis di MySkill. Data yang digunakan adalah data yang berasal dari Kaggle, yaitu Pakistan’s Largest E-Commerce Dataset dengan beberapa perubahan seperti harga yang tertera sudah dikonversi 1 Rupee = Rp58. Jika ingin mendapatkan file yang saya gunakan dapat dilihat di github yang sudah saya siapkan, yaitu disini. Data yang akan digunakan pada studi kasus ini antara lain order_detail, sku_detail, customer_detail, dan payment_detail.

Berikut merupakan penjelasan dari dataset yang digunakan:

Sebelum melakukan pengolahan data mari kita buat sebuah database untuk menampung dataset yang akan digunakan dengan cara klik kanan “Database” -> klik “Create” -> klik “Database…”.

Membuat database didalam PostgreSQL

Kemudian isi nama “Database” lalu tekan “Save”.

Jika database sudah dibuat langkah selanjutnya adalah melakukan import untuk membuat tabel yang akan digunakan dengan cara klik kanan pada database yang telah dibuat lalu klik “Restore…”. Cara ini adalah opsional untuk dilakukan karena masih ada cara lain untuk membuat tabel.

Mengimpor data

Lalu pilih file database yang akan digunakan kemudian klik “Restore”, kemudian akan muncul sebuah jendela baru untuk meng-upload file data yang akan digunakan.

Upload database yang akan digunakan

Jika proses restore data sudah selesai, maka akan muncul sebuah tabel yang nantinya akan diolah pada subfile Tables didalam Schemas database yang kita telah buat seperti gambar dibawah ini.

Database yang berhasil direstore

Selanjutnya kita dapat melihat data yang tersedia didalam tabel dengan query tool yang tersedia dan memasukan perintah:

SELECT *
FROM order_detail

Berdasarkan query diatas dapat diketahui bahwa:

• Perintah SELECT digunakan untuk memilih data yang akan ditampilkan kedalam kolom.
• Tanda * digunakan untuk memilih semua kolom yang ditampilkan.
• Perintah FROM digunakan untuk memilih sumber tabel yang akan diambil.
• order_detail merupakan database yang menjadi sumber.

Kemudaian jalankan query tersebut dan akan menampilkan data pada tabel order_detail seperti gambar dibawah ini.

Isi tabel order_detail

Lakukan hal yang sama pada tabel sku_detail, customer_detail, dan payment_detail untuk melihat isi tabel.

Menampilkan isi data dalam sku_detail.

SELECT *
FROM sku_detail

Isi tabel sku_detail

Menampilkan isi data dalam customer_detail.

SELECT *
FROM customer_detail

Isi tabel customer_detail

Menampilkan isi data dalam payment_detail.

SELECT *
FROM payment_detail

Isi tabel payment_detail

Setelah mengetahui isi dari dataset yang akan digunakan selanjutnya adalah mengerjakan beberapa studi kasus sesuai kondisi yang dibutuhkan.

Soal 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.

Source table: order_detail

Jawab:

SELECT
 to_char(order_date, 'Month') AS month_2021,
 ROUND(CAST(SUM(after_discount) AS numeric),2) AS total_sales
FROM
 order_detail 
WHERE
 is_valid = 1
 AND to_char(order_date,'yyyy-mm-dd') BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY 1
ORDER BY 2 DESC

Berdasarkan query diatas dapat kita analisis menjadi beberapa bagian mulai dari query SELECT.

• to_char(order_date, ‘Month’) AS month_2021, digunakan untuk mengambil data setiap bulan dengan format “Month” dimana sebelumnya telah diubah tipe data date dari “order_date” menjadi string dengan fungsi “to_char”. “AS” digunakan untuk memberi alias nama kolom dengan nama “month_2021”
• ROUND(CAST(SUM(after_discount) AS numeric),2) AS total_sales, digunakan untuk mengambil total nilai transaksi yang terjadi dengan menjumlahkan data pada “after_discount” dan dibuat nama kolom menjadi “total_sales” dengan fungsi “AS”. “ROUND” digunakan untuk membulatkan dengan 2 angka dibelakang koma yang sebelumnya telah diubah data tersebut menjadi “numeric” value.

Perintah FROM digunakan untuk mengambil data pada tabel “order_detail”.

Perintah WHERE digunakan untuk menyaring data berdasarkan:

• is_valid = 1, digunakan untuk menyaring data pelanggan yang telah melakukan pembayaran.
• AND, digunakan untuk melakukan syarat tambahan dalam menyaring data dengan masing masing kondisi bernilai TRUE.
• to_char(order_date,’yyyy-mm-dd’) BETWEEN ‘2021–01–01’ AND ‘2021–12–31’, digunakan untuk menyaring tanggal hanya di tahun 2021 dengan fungsi “BETWEEN”.

GROUP BY digunakan untuk melakukan pengelompokkan data yang belum teragregasi pada kolom ke-”1” atau “month_2021”.

ORDER BY digunakan untuk mengurutkan data berdasarkan kolom ke-”2" atau “total_sales” dari yang terbesar hingga terkecil dengan fungsi “DESC”.

Untuk opsional dapat menambahkan perintah “LIMIT = 1” dibawah perintah “ORDER BY” untuk membatasi hasil keluaran pada data dengan menampilkan hanya “1” data teratas. Namun pada kasus kali ini tidak akan digunakan agar dapat melihat semua total nilai transaksi disetiap bulan.

Dapat dilihat pada gambar dibawah ini merupakan hasil dari menjalankan query diatas.

Total nilai transaksi pada setiap bulan ditahun 2021

Jadi, dapat disimpulkan bahwa total nilai transaksi paling besar di 2021 terjadi pada bulan November dengan nilai 4783379762,46.

Soal 2

Selama transaksi yang terjadi selama 2021, pada bulan apa total jumlah pelanggan (unique), total order (unique) dan total jumlah kuantitas produk paling banyak?
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail

Jawab:

SELECT
  to_char(od.order_date, 'Month') month_2021,
  COUNT(DISTINCT od.customer_id) total_pelanggan,
  COUNT(DISTINCT od.id) total_pesanan,
  SUM(od.qty_ordered) total_kuantitas
FROM 
  order_detail od
WHERE
  is_valid = 1
  AND to_char(order_date,'yyyy-mm-dd') between '2021-01-01' AND '2021-12-31'
GROUP BY 1
ORDER BY 2 DESC

Berdasarkan query diatas dapat kita analisis menjadi beberapa bagian selain yang sudah dijelaskan di soal sebelumnya yaitu pada SELECT:

• to_char(od.order_date, ‘Month’) month_2021, digunakan untuk memilih data setiap bulan dari “order_date” dalam tabel “order_detail” alias “od” dan mengganti nama kolom menjadi “month_2021”.
• COUNT(DISTINCT od.customer_id) total_pelanggan, digunakan untuk memilih jumlah data unik pada data “customer_id” dan mengganti nama kolom menjadi “total_pelanggan”.
• COUNT(DISTINCT od.id) total_pesanan, digunakan untuk memilih jumlah data unik pada data “id” dan mengganti nama kolom menjadi “total_pesanan”.
• SUM(od.qty_ordered) total_kuantitas, digunakan untuk menjumlahkan data dari “qty_ordered” dan mengganti nama kolom menjadi “total_kuantitas”.

Perintah FROM digunakan untuk mengambil data pada tabel “order_detail” dengan alias “od”.

Dapat dilihat pada gambar dibawah ini merupakan hasil dari menjalankan query diatas.

Nilai yang menampilkan total pelanggan, total pesanan, dan total kuantitas untuk setiap bulan ditahun 2021

Jadi, dapat disimpulkan bahwa total jumlah pelanggan (unique), total order (unique) dan total jumlah kuantitas produk paling banyak pada tahun 2021 terjadi pada bulan November dengan jumlah pelanggan 13885, total order 22414, dan total kuantitas produk 47385.

Soal 3

Selama transaksi yang terjadi selama 2022, kategori apa yang menghasilkan nilai transaksi paling besar?
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail, sku_detail

Jawab:

SELECT
  sd.category,
  ROUND(CAST(SUM(od.after_discount) AS NUMERIC), 2) total_sales
FROM 
  order_detail AS od
LEFT JOIN 
  sku_detail AS sd ON sd.id = od.sku_id
WHERE
  is_valid = 1
  AND to_char(order_date,'yyyy-mm-dd') BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY 1
ORDER BY 2 DESC

Berdasarkan query diatas dapat kita analisis menjadi beberapa bagian selain yang sudah dijelaskan pada soal sebelumnya mulai dari query SELECT.

• sd.category, digunakan untuk memilih semua baris pada kolom “category”.
• ROUND(CAST(SUM(od.after_discount) AS NUMERIC), 2) total_sales, digunakan untuk memilih semua baris dengan jumlah total nilai transaksi pada kolom “after_discount”.

Perintah FROM digunakan untuk mengambil data pada tabel dengan ketentuan sebagai berikut:

• Data yang diambil berdasarkan isi data pada tabel “order_detail” alias “od”.
• Fungsi LEFT JOIN” digunakan untuk menggabungkan sebuah tabel dengan parameter tabel yang disebelah kiri.
• Tabel yang digabungkan kesebelah kiri dengan fungsi “LEFT JOIN” adalah tabel “sku_detail” alias “sd”.
• Data yang dicocokan atau digabung pada fungsi “LEFT JOIN” adalah data “id” pada tabel “sku_detail” dengan parameter data “sku_id” pada tabel “order_detail”.

Berbeda dengan soal 1 dan soal 2 dimana pada soal kali ini data yang ingin disaring adalah data pada tahun 2022 dengan menggunakan perintah “WHERE” seperti dibawah ini:

• to_char(order_date,’yyyy-mm-dd’) BETWEEN ‘2022–01–01’ AND ‘2022–12–31’, digunakan untuk mengambil data hanya ditahun 2022.

Setelah melakukan analisis kita dapat menjalaankan query diatas dan akan menghasilkan keluaran seperti pada gambar berikut ini.

Total nilai transaksi pada setiap kategori ditahun 2022

Jadi, dapat disimpulkan bahwa kategori yang menghasilkan nilai transaksi paling besar pada tahun 2022 adalah Mobiles & Tablets dengan nilai 8556323756,62.

Soal 4

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.

Source table: order_detail, sku_detail

Jawab:

WITH t1 AS
(
  SELECT
    sd.category,
    ROUND(CAST(SUM(CASE WHEN to_char(order_date,'yyyy-mm-dd') BETWEEN '2021-01-01' AND '2021-12-31' THEN od.after_discount END) AS NUMERIC), 2) total_sales_2021,
    ROUND(CAST(SUM(CASE WHEN to_char(order_date,'yyyy-mm-dd') between '2022-01-01' AND '2022-12-31' THEN od.after_discount END) AS NUMERIC), 2) total_sales_2022
  FROM 
    order_detail od
  LEFT JOIN 
    sku_detail sd ON sd.id = od.sku_id
  WHERE
    is_valid = 1
  GROUP BY 1
  ORDER BY 2 desc
)
SELECT
 t1.*,
 total_sales_2022 - total_sales_2021 growth_value
FROM t1
ORDER BY 4 DESC

Pada query tersebut digunakan sebuah fungsi WITH di awal yang bertujuan untuk membuat sebuah nested tabel atau tabel sementara untuk membungkus isi data yang ada didalam perintah WITH tersebut. Untuk memudahkan menganalisis query diatas mari kita pisahkan menjadi beberapa bagian.

Bagian Pertama

Dapat dilihat didalam perintah WITH tersebut terdapat sebuah query yang dibungkus didalam tanda kurung. Untuk lebih jelasnya berikut adalah perintah yang dimaksud:

SELECT
  sd.category,
  ROUND(CAST(SUM(CASE WHEN to_char(order_date,'yyyy-mm-dd') BETWEEN '2021-01-01' AND '2021-12-31' THEN od.after_discount END) AS NUMERIC), 2) total_sales_2021,
  ROUND(CAST(SUM(CASE WHEN to_char(order_date,'yyyy-mm-dd') between '2022-01-01' AND '2022-12-31' THEN od.after_discount END) AS NUMERIC), 2) total_sales_2022
FROM 
  order_detail od
LEFT JOIN 
  sku_detail sd ON sd.id = od.sku_id
WHERE
  is_valid = 1
GROUP BY 1
ORDER BY 2 desc

Query diatas bertujuan untuk mengambil total nilai transaksi yang terjadi di tahun 2021 dan 2022. Mari kita analisis query tersebut dimulai dari perintah SELECT.

• sd.category, digunakan untuk memilih semua baris pada kolom “category”
• ROUND(CAST(SUM(CASE WHEN to_char(order_date,’yyyy-mm-dd’) BETWEEN ‘2021–01–01’ AND ‘2021–12–31’ THEN od.after_discount END) AS NUMERIC), 2) total_sales_2021, digunakan untuk memilih total transaksi yang terjadi pada tahun 2021 dari setiap kategori. Dapat dilihat pada query tersebut menggunakan perintah “CASE” yang digunakan untuk membuat sebuah kondisi dengan mengambil data ditahun 2021 dengan fungsi “WHEN ”dan dimasukan total nilai transaksi pada “after_discount” dengan fungsi “THEN”. Untuk mengakhiri perintah “CASE ”maka digunakan “END” diakhir query.
• ROUND(CAST(SUM(CASE WHEN to_char(order_date,’yyyy-mm-dd’) between ‘2022–01–01’ AND ‘2022–12–31’ THEN od.after_discount END) AS NUMERIC), 2) total_sales_2022, digunakan untuk memilih total transaksi yang terjadi pada tahun 2022 dari setiap kategori.

Untuk mempermudah menganalisa bagian selanjutnya maka kita dapat mengibaratkan query diatas sebagai nilai “X” untuk contoh.

Bagian Kedua

Pada bagian ini kita akan menggabungkannya dengan query bagian pertama namun dengan inisial “X” sebagai contoh agar memudahkan dalam menganalisa.

WITH t1 AS
(
  "X" --ISI QUERY BAGIAN PERTAMA
)
SELECT
 t1.*,
 total_sales_2022 - total_sales_2021 growth_value
FROM t1
ORDER BY 4 DESC

Disinilah saatnya kita menggunakan perintah WITH untuk membungkus isi query pada bagian pertama dan dapat kita buat sebagai isi tabel sementara.

• WITH t1 AS, digunakan untuk menyimpan isi data “X” dengan nama “t1”. “X” adalah isi query pada bagian pertama.
• t1.*, digunakan untuk memilih semua data pada tabel “t1”.
• total_sales_2022 — total_sales_2021 growth_value, digunakan untuk mencari selisih antara total transaksi dari setiap kategori pada tahun 2022 dan 2021. Kolom tersebut dibuat nama menjadi “growth_value”.

Berdasarkan hasil analisis dari kedua bagian diatas kita dapat menjalankan semua query tersebut dan akan menghasilkan data seperti gambar dibawah ini.

Total nilai transaksi dan pertumbuhan pada setiap kategori ditahun 2021 dan 2022

Jadi, dapat disimpulkan berdasarkan nilai transaksi yang terjadi pada tahun 2021 dan 2022 bahwa kategori yang mengalami peningkatan adalah Mobiles & Tablets dengan nilai 2246769988,22 dan kategori yang mengalami penurunan adalah Others dengan nilai -97090149,80.

Soal 5

Tampilkan top 10 sku_name (beserta kategorinya) berdasarkan nilai transaksi yang terjadi selama tahun 2022. Tampilkan juga total jumlah pelanggan (unique), total order (unique) dan total jumlah kuantitas.
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail, sku_detail

Jawab:

SELECT 
  sd.sku_name,
  sd.category,
  ROUND(CAST(SUM(od.after_discount) AS NUMERIC), 2) total_sales,
  COUNT(DISTINCT od.customer_id) total_pelanggan,
  COUNT(DISTINCT od.id) total_order,
  SUM(od.qty_ordered) total_kuantitas
FROM 
  order_detail od
LEFT JOIN 
  sku_detail sd ON sd.id = od.sku_id
WHERE
  is_valid = 1
  AND to_char(order_date,'yyyy-mm-dd') BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY 1,2
ORDER BY 3 DESC
LIMIT 10

Tidak jauh berbeda seberti soal-soal sebelumnya, berdasarkan query diatas dapat kita analisis singkat menjadi:

• Memilih sebuah kolom yang ditampilkan dengan fungsi SELECT yang berisi “sku_name”, “category”, “total_sales”, “total_pelanggan”, “total_order”, dan “total_kuantitas”. Pada data “total_pelanggan” dan “total_order” hanya dijumlah berdasarkan banyaknya data unik berturut-turut pada “customer_id” dan “id” didalam tabel “order_detail”. Pada “total_kuantitas” dijumlah seluruh nilai pada “qty_ordered” pada tabel “order_detail”.
• Mengambil data dari tabel “order_detail” menggunakan perintah FROM dan dilakukan “LEFT JOIN” pada tabel “sku_detail”. Data yang di gabungkan adalah “id” pada “sku_detail” dengan parameter “sku_id” pada “order_detail”.
• Selanjutnya menyaring data dengan “is_valid = 1” dan diambil data pada tahun 2022
• GROUP BY dilakukan untuk mengelompokkan data yang belum teragregasi pada kolom “1” dan “2”.
• Diurutkan menggunakan ORDER BY dari yang terbesar ke terkecil menggunakan “DESC” berdasarkan kolom “3”
• Yang terakhir dilakukan pembatasan data yang ditampilkan menggunakan “LIMIT” sebanyak “10” data.

Dapat dilihat pada gambar dibawah ini merupakan hasil dari menjalankan query diatas.

Top 10 sku_name berdasarkan nilai transaksi pada tahun 2022

Jadi, dapat disimpulkan bahwa Top 10 sku_name berdasarkan nilai transaksi yang terjadi selama tahun 2022 dalah sebagai berikut:
1. IDROID_BALRX7-Gold nilai transaksi sebesar 865224543.90
2. IDROID_BALRX7-Jet black nilai transaksi sebesar 525626251.06
3. infinix_Zero 4-Grey nilai transaksi sebesar 443381130.50
4. IDROID_BALRX7-Jet black nilai transaksi sebesar 298041331.70
5. iphone-7–32gb-wof-Matt Black nilai transaksi sebesar 289575962.00
6. Infinix Hot 4-Black nilai transaksi sebesar 278470817.42
7. Infinix Hot 4-Gold nilai transaksi sebesar 272945142.92
8. AYS_32B8500–32-Inches nilai transaksi sebesar 192536626.00
9. closecomfort_PC8 nilai transaksi sebesar 169312440.00
10. Haier_HSU-18-HNF nilai transaksi sebesar149577215.00

Soal 6

Tampilkan top 5 metode pembayaran yang paling populer digunakan selama 2022 (berdasarkan total unique order).
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail, payment_method

Jawab:

SELECT 
  pd.payment_method,
  COUNT(DISTINCT od.id) total_pelanggan
FROM 
  order_detail od
  LEFT JOIN payment_detail pd ON pd.id = od.payment_id
WHERE
  is_valid = 1
  AND to_char(order_date,'yyyy-mm-dd') BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5

Untuk fungsi dan kegunaan dari query diatas kurang lebih sama seperti yang sudah dijelaskan pada soal-soal sebelumnya, maka kita dapat langsung menjalankannya dengan hasil sebagai berikut:

Top 5 metde pembayaran populer pada tahun 2022

Jadi, dapat disimpulkan bahwa top 5 metode pembayaran yang paling populer pada tahun 2022 adalah sebagai berikut:
1. cod total pelanggan 42609
2. Payaxis total pelanggan 5341
3. Easypay total pelanggan 1443
4. customercredit total pelanggan 1378
5. jazzwallet total pelanggan 1368

Soal 7

Urutkan dari ke-5 produk ini berdasarkan nilai transaksinya.
1. Samsung
2. Apple
3. Sony
4. Huawei
5. Lenovo
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail, sku_detail

Jawab:

WITH a AS 
(
  SELECT 
    CASE 
      WHEN LOWER(sd.sku_name) LIKE '%samsung%' THEN 'Samsung'
      WHEN LOWER(sd.sku_name) LIKE '%apple%' OR LOWER(sd.sku_name) LIKE '%iphone%' THEN 'Apple'
      WHEN LOWER(sd.sku_name) LIKE '%sony%' THEN 'Sony'
      WHEN LOWER(sd.sku_name) LIKE '%huawei%' THEN 'Huawei'
      WHEN LOWER(sd.sku_name) LIKE '%lenovo%' THEN 'Lenovo'
    END product_brand,
    ROUND(CAST(SUM(od.after_discount) AS NUMERIC), 2) total_sales
  FROM 
    order_detail od
  LEFT JOIN 
    sku_detail sd ON sd.id = od.sku_id
  WHERE
    is_valid = 1
  GROUP BY 1
)
SELECT 
  a.*
FROM 
  a
WHERE 
  product_brand NOTNULL
ORDER BY 2 DESC

Untuk penyelesaian pada soal ini menggunakan fungsi WITH seperti hal nya pada soal 4. Untuk memudahkan menganalisa mari kita bagi menjadi beberapa bagian.

Bagian Pertama

Queri yang digunakan didalam perintah WITH adalah sebagai berikut:

SELECT 
    CASE 
      WHEN LOWER(sd.sku_name) LIKE '%samsung%' THEN 'Samsung'
      WHEN LOWER(sd.sku_name) LIKE '%apple%' OR LOWER(sd.sku_name) LIKE '%iphone%' THEN 'Apple'
      WHEN LOWER(sd.sku_name) LIKE '%sony%' THEN 'Sony'
      WHEN LOWER(sd.sku_name) LIKE '%huawei%' THEN 'Huawei'
      WHEN LOWER(sd.sku_name) LIKE '%lenovo%' THEN 'Lenovo'
    END product_brand,
    ROUND(CAST(SUM(od.after_discount) AS NUMERIC), 2) total_sales
  FROM 
    order_detail od
  LEFT JOIN 
    sku_detail sd ON sd.id = od.sku_id
  WHERE
    is_valid = 1
  GROUP BY 1

Berdasarkan query tersebut dapat dianalisis seperti berikut ini selain yang sudah dijelaskan pada soal sebelum-sebelumnya.

• Menampilkan seluruh baris pada kolom “product_brand” yang dibuat sebuah kondisi statement menggunakan perintah “CASE” dan diakhiri dengan perintah “END”.
• Didalam syarat “WHEN” digunakan fungsi “LOWER” untuk merubah huruf menjadi kecil atau lowercase.
• Fungsi LIKE digunakan untuk mencari sebuah pola yang ditentukan didalam kolom. Menurut penggunaannya dengan menyisipkan tanda “%” diawal, diakhir atau diantara pola yang ingin dicocokan berdasarkan fungsinya masing-masing.
• Jika syarat bernilai TRUE maka akan diteruskan ke fungsi THEN untuk diubah menjadi kondisi yang diinginkan. Misalnya seperti WHEN LOWER(sd.sku_name) LIKE ‘%samsung%’ THEN ‘Samsung’, maka jika didalam kolom “product_brand” terdapat pola yang didalamnya bertuliskan “samsung” akan diubah menjadi “Samsung”.

Queri tersebut akan dimasukkan kedalam perintah WITH dan dibungkus kedalam sebuah tabel sementara yang dapat kita analogikan sebagai “X”.

Bagian Kedua

Kemudian kita dapat menganalisis queri keseluruhannya seperti dibawah ini.

WITH a AS 
(
  "X" --ISI QUERY BAGIAN PERTAMA
)
SELECT 
  a.*
FROM 
  a
WHERE 
  product_brand NOTNULL
ORDER BY 2 DESC

Berdasarkan query diatas dapat kita simpulkan sebagai berikut.

• WITH a AS, digunakan untuk membuat sebuah tabel sementara yang berisi query dari “X”.
• SELECT a.*, digunakan untuk memilih semua baris yang ada didalam tabel “a”.
• FROM a, digunakan untuk memilih sumber tabel yang akan digunakan.
• WHERE product_brand NOTNULL, digunakan untuk menyaring data yang NOTNULL atau tidak kosong.
• ORDER BY 2 DESC, digunakan untuk mengurutkan data berdasarkan kolom “2” dari yang terbesar

Dari keseluruhan queri tersebut kita dapat menjalankannya dan akan mendapatkan hasil seperti gambar dibawah ini:

5 produk teratas berdasarkan nilai transaksinya

Jadi, dapat disimpulkan bahwa urutan 5 produk diatas berdasarkan nilai transaksinya adalah sebagai berikut:
1. Samsung nilai transaksi sebesar 3757776955.30
2. Apple nilai transaksi sebesar 1670041035.10
3. Huawei nilai transaksi sebesar 1012497391.64
4. Lenovo nilai transaksi sebesar 393545796.88
5. Sony nilai transaksi sebesar 190611120.54

Soal 8

Seperti pertanyaan no. 3, buatlah perbandingan dari nilai profit tahun 2021 dan 2022 pada tiap kategori. Kemudian buatlah selisih % perbedaan profit antara 2021 dengan 2022 (profit = after_discount — (cogs*qty_ordered)).
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail, sku_detail

Jawab:

WITH b AS 
(
  WITH a AS 
  (
    SELECT
      od.id,
      to_char(od.order_date, 'yyyy') year_order,
      sd.category,
      od.after_discount - (sd.cogs*od.qty_ordered) profit
    FROM 
      order_detail od
    LEFT JOIN 
      sku_detail sd ON sd.id = od.sku_id
    WHERE
      is_valid = 1
  )
  SELECT 
    a.category,
    ROUND(CAST(SUM(CASE WHEN year_order = '2021' THEN a.profit END) AS NUMERIC), 2) profit_2021,
    ROUND(CAST(SUM(CASE WHEN year_order = '2022' THEN a.profit END) AS NUMERIC), 2) profit_2022
  FROM  
    a
  GROUP BY 1
)
SELECT 
  b.*,
  ROUND(CAST((b.profit_2022-b.profit_2021)/b.profit_2021 AS NUMERIC), 2) growth
FROM 
  b
ORDER BY 4 DESC

Berdasarkan query diatas kita dapat menganalisis singkat seperti pada soal sebelum-sebelumnya.

• Melakukan analisis data menampilkan semua profit dari setiap id order pada masing-masing kategori pada tahun 2021 dan 2022. Data tersebut dimasukkan dalam fungsi “WITH ”dan diberi nama variabel “a”.
• Kemudian, mengelompokan data terhadap kategori dengan masing-masing profit di tahun 2021 dan 2022 berdasarkan data pada tabel “a”. Data tersebut dimasukkan kedalam fungsi “WITH ”dan diberi nama variabel “b”.
• Setelah profit setiap kategori di masing-masing tahun didapatkan, selanjutnya menghitung persentase selisih dari perbedaan profit ditahun 2021 dengan 2022 dengan rumus profit = after_discount — (cogs*qty_ordered) dan dimasukkan kedalam kolom growth.

Berdasarkan analisa tersebut kita dapat menjalankan seluruh query dan berikut adalah hasil yang didapatkan.

Persentase selisih dari perbedaan profit ditahun 2021 dan 2022 dari terbesar hingga terkecil

Jadi, dapat disimpulkan untuk persentase selisih perbedaan profit ditahun 2021 dan 2022 adalah sebagai berikut:
1. Women Fashion dengan perbedaan profit 1.67%
2. Superstore dengan perbedaan profit 1.46%
3. Entertainment dengan perbedaan profit 0.43%
4. Computing dengan perbedaan profit 0.43%
5. Appliances dengan perbedaan profit 0.21%
6. Mobiles & Tablets dengan perbedaan profit 0.18%
7. Health & Sports dengan perbedaan profit 0.17%
8. School & Education dengan perbedaan profit 0.10%
9. Home & Living dengan perbedaan profit 0.08%
10. Beauty & Grooming dengan perbedaan profit 0.06%
11. Kids & Baby dengan perbedaan profit -0.17%
12. Men Fashion dengan perbedaan profit -0.19%
13. Others dengan perbedaan profit -0.22%
14. .Soghaat dengan perbedaan profit -0.29%
15. Books dengan perbedaan profit -0.57%

Soal 9

Tampilkan top 5 SKU dengan kontribusi profit paling tinggi di tahun 2022 berdasarkan kategori paling besar pertumbuhan profit dari 2021 ke 2022 (berdasarkan hasil no 8).
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail, sku_detail

Jawab:

WITH a AS 
(
  SELECT
    od.id,
    Sd.sku_name,
    od.after_discount - (sd.cogs*od.qty_ordered) profit
  FROM 
    order_detail od
  LEFT JOIN 
    sku_detail sd ON sd.id = od.sku_id
  WHERE
    is_valid = 1
    AND to_char(order_date, 'yyyy-mm-dd') BETWEEN '2022-01-01' AND '2022-12-31'
    AND sd.category = 'Women Fashion'
)
SELECT 
  a.sku_name,
  SUM(a.profit) total_profit
FROM 
  a
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5

Berdasarkan query diatas dapat kita analisis singkat menjadi sebagai berikut:

• Menganalisis dan menampilkan semua data dengan total profit dari id di setiap SKU. Kemudian dilakukan penyaringan dan hanya diambil data profit tertinggi di tahun 2022 dengan kategori “Woman Fashion”. Kategori “Woman Fashion” digunakan karena memiliki pertumbuhan profit terbesar dari tahun 2021 ke 2022 berdasarkan soal 8. Data ini kemudian dimasukkan kedalam fungi WITH dengan variabel “a”
• Dengan menggunakan data pada tabel sementara yang sudah dibuat yaitu “a”, selanjutnya menjumlahkan seluruh total profit dari setiap SKU. Terakhir dengan menggunakan LIMIT = 5, maka data yang ditampilkan hanya 5 teratas.

Dapat dilihat pada gambar dibawah ini merupakan hasil dari menjalankan query diatas.

5 SKU dengan profit tertinggi di tahun 2022

Jadi, dapat disimpulkan bahwa 5 SKU teratas dengan kontribusi profit paling tinggi di tahun 2022 berdasarkan kategori Woman Fashion adalah sebagai berikut:
1. sanasafinaz_SS-3A total profit sebesar 3498908
2. sanasafinaz_SS-9B total profit sebesar 3153692
3. sanasafinaz_SS-13A total profit sebesar 3013622
4. sanasafinaz_SS-5B total profit sebesar 2902320
5. sanasafinaz_SS-10A total profit sebesar 2871696

Soal 10

Tampilkan jumlah unique order yang menggunakan top 5 metode pembayaran (soal no 6) berdasarkan kategori produk selama tahun 2022.
Gunakan is_valid = 1 untuk memfilter data transaksi.

Source table: order_detail, payment_detail, sku_detail

Jawab:

SELECT
  sd.category,
  COUNT(DISTINCT CASE WHEN pd.payment_method = 'cod' THEN od.id END) cod,
  COUNT(DISTINCT CASE WHEN pd.payment_method = 'Easypay' THEN od.id END) easypay,
  COUNT(DISTINCT CASE WHEN pd.payment_method = 'Payaxis' THEN od.id END) payaxis,
  COUNT(DISTINCT CASE WHEN pd.payment_method = 'customercredit' THEN od.id END) customercredit,
  COUNT(DISTINCT CASE WHEN pd.payment_method = 'jazzwallet' THEN od.id END) jazzwallet
FROM 
  order_detail od
LEFT JOIN 
  payment_detail pd ON pd.id = od.payment_id
LEFT JOIN 
  sku_detail sd ON sd.id = od.sku_id
WHERE 
  is_valid = 1
  AND to_char(order_date, 'yyyy-mm-dd') BETWEEN '2022-01-01' AND '2022-12-31'
GROUP BY 1

Berdasarkan query diatas dapat kita analisis menjadi beberapa bagian mulai dari query SELECT.

• sd.category, digunakan untuk menampilkan data kategori didalam kolom “category”
• Menampilkan jumlah data unik dari setiap pembayaran untuk 5 metode pembayaran teratas, yaitu cod, easypay, payaxis, customercredit, dan jazzwallet.

Pada perintah FROM terdapat beberapa sumber tabel yang digunakan dengan menggunakan perintah LEFT JOIN.

• Sumber data yang digunakan berasal dari tabel “order_detail”, payment_detail, dan sku_detail.
• payment_detail dan sku_detail yang masing-masing digabungkan menggunakan LEFT JOIN berdasarkan id di masing-masing tabel dengan parameter payment_id dan sku_id pada order_detail.

Kemudian dilakukan penyaringan data menggunakan perintah WHERE dan mengambil data pada tahun 2022 yang sudah melakukan pembayaran.

Data dikelompokkan berdasarkan kategori dengan menggunakan perintah GROUP BY.

Untuk opsional dapat menambahkah perintah ORDER BY dibawah perintah GROUP BY untuk mengurutkan nilai dari yang terbesar ke terkecil atau sebaliknya berdasarkan kolom atau metode pembayaran yang ingin diketahui nilai terbesar atau terkecilnya. Namun disini saya tidak menggunakan ORDER BY karena disoal hanya diminta jumlah data unik setiap kategori berdasarkan metode pembayaran.

Dapat dilihat pada gambar dibawah ini merupakan hasil dari menjalankan query diatas.

Jumlah unique order yang menggunakan top 5 metode berdasarkan kategori ditahun 2022

Jadi, didapatkan data dengan jumlah unique order yang menggunakan top 5 metode berdasarkan kategori ditahun 2022 sebagai berikut:

1. Appliances → COD=2808, Easypay=175, Payaxis=893, Customcredit=131, Jazzwallet=58
2. Beauty & Grooming → COD=4125, Easypay=85, Payaxis=301, Customcredit=108, Jazzwallet=89
3. Books → COD=336 Easypay=4, Payaxis=10, Customcredit=4, Jazzwallet=4
4. Computing → COD=1287, Easypay=82, Payaxis=344, Customcredit=44, Jazzwallet=29
5. Entertainment → COD=1152, Easypay=129, Payaxis=547, Customcredit=45, Jazzwallet=35
6. Health & Sports → COD=2440, Easypay=41, Payaxis=268, Customcredit=67, Jazzwallet=40
7. Home & Living → COD=3314, Easypay=74, Payaxis=295, Customcredit=97, Jazzwallet=60
8. Kids & Baby → COD=2635, Easypay=81, Payaxis=306, Customcredit=66, Jazzwallet=85
9. Men Fashion → COD=10510, Easypay=186, Payaxis=928, Customcredit=300, Jazzwallet=195
10. Mobiles & Tablets → COD=9853, Easypay=618, Payaxis=1804, Customcredit=369, Jazzwallet=208
11. Others → COD=1066 Easypay=35, Payaxis=103, Customcredit=34, Jazzwallet=41
12. School & Education → COD=457, Easypay=11, Payaxis=40, Customcredit=9, Jazzwallet=9
13. Soghaat → COD=3065, Easypay=63, Payaxis=236, Customcredit=111, Jazzwallet=109
14. Superstore → COD=2138, Easypay=81, Payaxis=531, Customcredit=77, Jazzwallet=700
15. Women Fashion → COD=7599, Easypay=201, Payaxis=843, Customcredit=176, Jazzwallet=89

…

Berdasarkan studi kasus yang telah dikerjakan diatas, maka sampailah dipenghujung artikel mengenai Penggunaan SQL untuk seorang Data Analisis. Mengerjakan soal ini cukup menantang bagi saya dan sangat bermanfaat untuk mengasah kemampuan logika serta kemampuan problem solving dalam menganalisa sebuah data.

Untuk artikel menarik lainnya mengenai Data Analisis dan juga lanjutan Final Projek yang saya kerjakan pada Bootcamp Data Analysis MySkill, dapat dilihat pada link dibawah ini:

• Analisis dan Visualisasi Data Menggunakan Python
• Membuat Dashboard pada Looker Data Studio (Google Data Studio)

Referensi:

1. Amazon. What Is SQL; https://aws.amazon.com/what-is/sql/
2. W3School. SQL Tutorial; https://www.w3schools.com/sql/sql_intro.asp
3. James Coe. 2021. SQL Basic; https://www.dataquest.io/blog/sql-basics/
4. Loshin & Sirkin. 2022. Structured Query Language; https://www.techtarget.com/searchdatamanagement/definition/SQL#:~:text=Structured%20Query%20Language%20(SQL)%20is,on%20the%20data%20in%20them