MySkill Intensive Bootcamp Data Analisis SQL Exercise

Link Excercise 2, Excercise Phyton.

Link Excercize 3, Excercise Looker Studio.

SQL (Structured Query Language) adalah bahasa pemrograman untuk menyimpan dan memproses data di dalam sebuah database. Sebuah database menyimpan data dalam bentuk tabel, yang terdiri dari baris dan kolom yang memiliki atribut dan hubungan yang berbeda-beda. SQL Query dapat digunakan untuk menyimpan, memperbaharui, menghapus, mencari dan mengambil data dari database. SQL Query juga bisa digunakan untuk memelihara dan mengoptimalisasikan database.

Syntax atau fungsi yang paling penting dalam SQL Query adalah SELECT dan FROM, tanpa keduanya maka SQL Query tidak akan bisa berjalan. Dalam exercise SQL ini syntax yang akan digunakan adalah sebagai berikut

SELECT berfungsi untuk menampilkan data yang diinginkan

FROM berfungsi sebagai sumber tabel yang akan di tampilkan

WHERE berfungsi sebagai bagaimana kondisi data yang ingin ditampilkan

ORDER BY berfungsi untuk mengurutkan data yang ditampilkan, biasanya diikuti dengan fungsi ASC (ascending) atau DESC (descending)

GROUP BY berfungsi untuk menggabungkan kolom jika ada fungsi agregat

AS berfungsi untuk mengaliaskan sebuah query atau fungsi

EXTRACT berfungsi untuk mengambil hari/bulan/tahun dari data tanggal

COUNT berfungsi untuk menghitung berapa jumlah data yang ada

SUM berfungsi untuk menghitung total suatu data

LEFT JOIN berfungsi untuk menggabungkan data tabel A dengan data tabel B yang sama dengan tabel A, biasanya diikuti dengan fungsi ON untuk mendefinisikan kolom apa yang sama antara tabel A dan B

CASE WHEN berfungsi untuk kondisi Jika-Maka dalam sebuah query

DISTINCT berfungsi untuk tidak menampilkan data yang sama

LIMIT berfungsi untuk membatasi baris data yang ditampilkan

LOWER berfungsi membuat data menjadi lowercase

AND berfungsi untuk menggabungkan 2 kondisi atau lebih

Database yang digunakan dalam exercise ini adalah PostgreSQL

Tabel

Tabel yang digunakan dalam exercise ini sebagai berikut

order_detail:

1. id → angka unik dari order / id_order
2. customer_id → angka unik dari pelanggan
3. order_date → tanggal saat dilakukan transaksi
4. sku_id → angka unik dari produk (sku adalah stock keeping unit)
5. price → harga yang tertera pada tagging harga
6. qty_ordered → jumlah barang yang dibeli oleh pelanggan
7. before_discount → nilai harga total dari produk (price * qty_ordered)
8. discount_amount → nilai diskon product total
9. after_discount → nilai harga total produk ketika sudah dikurangi dengan diskon
10. is_gross → menunjukkan pelanggan belum membayar pesanan
11. is_valid → menunjukkan pelanggan sudah melakukan pembayaran
12. is_net → menunjukkan transaksi sudah selesai
13. payment_id → angka unik dari metode pembayaran

sku_detail:

1. id → angka unik dari produk (dapat digunakan untuk key saat join)
2. sku_name → nama dari produk
3. base_price → harga barang yang tertera pada tagging harga / price
4. cogs → cost of goods sold / total biaya untuk menjual 1 produk
5. category → kategori produk

customer_detail:

1. id → angka unik dari pelanggan
2. registered_date → tanggal pelanggan mulai mendaftarkan diri sebagai anggota

Payment_detail:

1. id → angka unik dari metode pembayaran
2. payment_method → metode pembayaran yang digunakan

Soal dan Jawaban

Q1: 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

Dari soal diatas kita diminta untuk menampilan bulan dan total after_discount yang berarti SELECT dan SUM, sumber tabel order_detail yang berarti FROM, dengan kondisi is_valid=1 dan transaksi selama 2021 yang berarti WHERE, dan total after_discount paling besar yang berarti ORDER BY. Sehingga dapat kita membuat query sebagai berikut

SELECT TO_CHAR(ORDER_DATE, 'Month') AS MONTH,
 SUM (AFTER_DISCOUNT)
FROM ORDER_DETAIL
WHERE IS_VALID = 1
 AND EXTRACT(YEAR FROM ORDER_DATE) = 2021
GROUP BY MONTH
ORDER BY SUM (AFTER_DISCOUNT) DESC

Yang menghasilkan data sebagai berikut

dapat dilihat bahwa bulan November merupakan bulan dimana total nilai transaksi terbesar di tahun 2021.

Q2: 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

Dari soal di atas kita diminta untuk memampilkan bulan, total jumlah pelanggan (unique), total order (unique) dan total jumlah kuantitas produk yang berarti SELECT, SUM, COUNT dan DISCTINCT, sumber tabel order_detail yang berarti FROM, kondisi is_valid=1 dan transaksi selama 2021 yang berarti WHERE, “Paling Banyak” berarti ORDER BY. Sehingga dapat kita membuat query sebagai berikut

SELECT TO_CHAR(OD.ORDER_DATE, 'Month') AS MONTH_2021,
 COUNT(DISTINCT OD.CUSTOMER_ID) AS TOTAL_PELANGGAN,
 COUNT(DISTINCT OD.ID) AS TOTAL_PESANAN,
 SUM(OD.QTY_ORDERED) AS TOTAL_KUANTITAS
FROM ORDER_DETAIL AS OD
WHERE IS_VALID = 1
 AND EXTRACT(YEAR FROM ORDER_DATE) = 2021
GROUP BY 1
ORDER BY 2 DESC;

Yang menghasilkan data sebagai berikut

dapat dilihat bahwa bulan November merupakan bulan dimana total pelanggan, total order dan total jumlah kualitas produk terbesar di tahun 2021.

Q3: 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

Dari soal di atas kita diminta untuk menampikan kategori dan nilai transaksi yang berarti SELECT dan SUM, sumber tabel order_detail dan sku_detail yang berarti FROM dan LEFT JOIN, kondisi is_valid=1 dan transaksi selama 2022 berarti WHERE, dan nilai transaksi paling besar berarti ORDER BY. Sehingga kita dapat membauat query seperti berikut

SELECT SD.CATEGORY,
 SUM(OD.AFTER_DISCOUNT)
FROM ORDER_DETAIL AS OD
LEFT JOIN SKU_DETAIL AS SD ON OD.SKU_ID = SD.ID
WHERE IS_VALID = 1
 AND EXTRACT(YEAR FROM OD.ORDER_DATE) = 2022
GROUP BY 1
ORDER BY 2 DESC;

Yang akan menghasilkan data sebagai berikut

dapat dilihat bahwa kategori yang menghasilkan nilai transaksi terbesar adalah Mobiles & Tablets.

Q4: 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

Dari soal diatas kita diminta untuk menampikan kategori, dan kategori apa yang mengalami kenaikan dan penurutan yang berarti SELECT dan SUM, sumber tabel order_detail dan sku_detail berarti FROM dan LEFT JOIN, kondisi is_valid=1 berarti WHERE. Sehingga kita dapat membuat query seperti berikut

WITH SRC AS
 (SELECT SD.CATEGORY,
   SUM(CASE WHEN EXTRACT(YEAR FROM OD.ORDER_DATE) = 2021 
    THEN OD.AFTER_DISCOUNT END) AS TOTAL_2021,
   SUM(CASE WHEN EXTRACT(YEAR FROM OD.ORDER_DATE) = 2022 
    THEN OD.AFTER_DISCOUNT END) AS TOTAL_2022
  FROM ORDER_DETAIL AS OD
  LEFT JOIN SKU_DETAIL AS SD ON OD.SKU_ID = SD.ID
  WHERE IS_VALID = 1
  GROUP BY 1)
SELECT CATEGORY,
 TOTAL_2021,
 TOTAL_2022, (CASE WHEN TOTAL_2022 > TOTAL_2021 
     THEN 'Peningkatan' ELSE 'Penurunan' END) as growth
FROM SRC;

Yang menghasilkan data sebagai berikut

Dari gambar di atas dapat dilihat kategori yang mengalami peningkatan atau penurutan dari tahun 2021 ke tahun 2022.

Q5: 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

Dari soal di atas kita diminta untuk menampilkan sku_name, kategori, total jumlah pelanggan, total order, dan total jumlah kuantitas yang berarti SELECT, SUM, COUNT dan DISTINCT, sumber tabel dari order_detail dan sku_detail yang berarti FROM dan LEFT JOIN, is_valid=1 dan terjadi selama tahun 2022 berarti WHERE, Top 10 berarti ORDER BY DESC dan LIMIT. sehingga kita dapat membuat query seperti berikut

SELECT SD.SKU_NAME,
 SD.CATEGORY,
 SUM(OD.AFTER_DISCOUNT) AS NILAI_TRX,
 COUNT(DISTINCT OD.CUSTOMER_ID) AS TOTAL_PELANGGAN,
 COUNT(DISTINCT OD.ID) AS TOTAL_PELANGGAN,
 SUM(OD.QTY_ORDERED)
FROM ORDER_DETAIL AS OD
LEFT JOIN SKU_DETAIL AS SD ON OD.SKU_ID = SD.ID
WHERE IS_VALID = 1
 AND EXTRACT(YEAR FROM OD.ORDER_DATE) = 2022
GROUP BY 1, 2
ORDER BY 3 DESC
LIMIT 10

Yang menghasilkan data sebagai berikut

Dari gambar di atas dapat dilihat top sku_name berserta kategoti, nilai transaksi, total pelanggan, total order dan total jumlah kuantitas.

Q6: 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

Dari soal diatas kita diminta untuk menampilkan metode pembayaran, total unik order yang berarti SELECT COUNT DISTINCT, sumber tabel order_detail dan payment_method yang berarti FROM dan LEFT JOIN, kondisi is_valid=1 dan selama 2022 yang berarti WHERE, Top 5 berarti ORDER BY DESC dan LIMIT. Sehingga kita dapat membuat query seperti berikut

SELECT PD.PAYMENT_METHOD,
 COUNT(DISTINCT OD.ID) TOTAL_UNIQUE_ORDER
FROM ORDER_DETAIL AS OD
LEFT JOIN PAYMENT_DETAIL AS PD ON OD.PAYMENT_ID = PD.ID
WHERE IS_VALID = 1
 AND EXTRACT(YEAR FROM OD.ORDER_DATE) = 2022
GROUP BY 1
ORDER BY 2 DESC
LIMIT 5;

Yang menghasilkan data sebagai berikut

Dari gambar diatas dapat dilihat top 5 payment method selama tahun 2022.

Q7: 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

Dari soal diatas kita diminta untuk menampilkan sku_name tertentu dan total nilai transaksi yang berarti SELECT, CASE WHEN dan SUM, sumber table order_detail dan sku_detail yang berarti FROM dan LEFT JOIN, kondisi is_valid=1 berarti WHERE dan Urutkan ke-5 produk berarti ORDER BY DESC. sehingga kita dapat membuat query seperti berikut

WITH BRAND AS
 (SELECT ID,
   (CASE
    WHEN LOWER(SKU_NAME) like '%samsung%' THEN 'Samsung'
    WHEN LOWER(SKU_NAME) like '%apple%' THEN 'Apple'
    WHEN LOWER(SKU_NAME) like '%sony%' THEN 'Sony'
    WHEN LOWER(SKU_NAME) like '%huawei%' THEN 'Huawei'
    WHEN LOWER(SKU_NAME) like '%lenovo%' THEN 'Lenovo'
    END) AS BRAND_NAME
  FROM SKU_DETAIL)
SELECT BRAND_NAME,
 SUM(AFTER_DISCOUNT) AS TOTAL_TRX
FROM ORDER_DETAIL AS OD
LEFT JOIN BRAND AS BD ON OD.SKU_ID = BD.ID
WHERE BRAND_NAME IS NOT NULL
 AND IS_VALID = 1
GROUP BY 1
ORDER BY 2 DESC;

Yang menghasilkan data sebagai berikut

Dari gambar di atas dapat dilihat urutan ke-5 produk berdasarkan nilai transaksinya.

Q8: 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

Dari soal di atas kita diminta untuk menampikan kategori, profit tahun 2021, profit tahun 2022, dan selisih profit 2021 dan 2022 yang berarti SELECT, SUM dan CASE WHEN, sumber tabel order_detail dan sku_detail yang berarti FROM dan LEFT JOIN, kondisi is_valid=1 yang berarti WHERE. sehingga kita dapat membuat query sebagai berikut

WITH PR AS
 (WITH SRC AS
   (SELECT OD.ORDER_DATE,
     SD.CATEGORY,
     (OD.AFTER_DISCOUNT - (SD.COGS * OD.QTY_ORDERED)) AS PROFIT
    FROM ORDER_DETAIL AS OD
    LEFT JOIN SKU_DETAIL AS SD ON OD.SKU_ID = SD.ID
    WHERE IS_VALID = 1 ) 
  SELECT SRC.CATEGORY,
   SUM(CASE WHEN EXTRACT(YEAR FROM SRC.ORDER_DATE) = 2021 
    THEN SRC.PROFIT END) AS PROFIT_2021,
   SUM(CASE WHEN EXTRACT(YEAR FROM SRC.ORDER_DATE) = 2022 
    THEN SRC.PROFIT END) AS PROFIT_2022
  FROM SRC
  GROUP BY SRC.CATEGORY)
SELECT *,
 (PROFIT_2022 - PROFIT_2021) / PROFIT_2021 AS GROWTH
FROM PR

Yang menghasilkan data sebagai berikut

Dari gambar di atas dapat dilihat profit 2021, profit 2022, dan growth per kategori yang ada.

Q9: 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

Dari soal diatas kita diminta untuk menampilkan sku_name, total profit 2021 ke 2022 yang berarti SELECT, dan SUM, sumber data dari tabel order_detail dan sku_detail yang bearti FROM dan LEFT JOIN, kondisi is_valid=1 dan kontribusi profit di tahun 2022, kategori profit paling besar no 8 adalah “Women Fashion” yang berarti WHERE, Top 5 yang berarti ORDER BY DESC dan LIMIT. sehingga kita dapat membuat query sebagai berikut

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 EXTRACT(YEAR FROM ORDER_DATE) = 2022
   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

Yang menghasilkan data sebagai berikut

Dari gambar diatas dapat dilihat top 5 sku_name dengan total profit tertinggi.

Q10: 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, sku_detail

Dari soal diatas kita diminta untuk menampilkan kategori, top 5 metode pembayaran dari soal no 6 yang berarti SELECT, COUNT, DISTINCT, dan CASE WHEN, sumber data dari tabel order_detail dan sku_detail yang berarti FROM dan LEFT JOIN, kondisi is_valid=1 dan selama 2022 yang berarti WHERE. sehingga kita dapat membuat query seperti berikut

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 EXTRACT(YEAR FROM ORDER_DATE) = 2022
GROUP BY 1
ORDER BY 2 DESC;

Yang menghasilkan data sebagai berikut

Dari gambar diatas dapat dilihat jumlah order yang menggunakan top 5 metode pembayaran beserta kategorinya.

Demikianlah jawaban dan sedikit penjelasan dari saya terhadap SQL Excercise Bootcamp ini, Terima Kasih.