Data Engineer Challenge with SQL

Photo by Caspar Camille Rubin on Unsplash

Haii kembali lagi bersama saya!! disini saya akan membahas Challenge SQL dari DQLab Academy, yang tentunya akan membahas tentang SQL lebih dalam lagi terkait Case Perusahaan, yukk langsung saja kita bahas…

Data yang digunakan

Ada 4 tabel yang tersedia, antara lain :

1. ms_pelanggan berisi tentang identitas pelanggan. berisi,

• no_urut : row pada tabel
• kode_pelanggan : kode setiap pelanggan yang bertransaksi
• nama_pelanggan : nama dari pelanggan yang bertransaksi
• alamat : tempat pelanggan tinggal

2. ms_produk berisi tentang detail pada produk. berisi,

• no_urut : row pada tabel
• kode_produk : kode yang tertera pada produk
• nama_produk : nama produk yang diperjualbelikan
• harga : harga pada produk yang diperjualbelikan

3. tr_penjualan berisi tentang transaksi penjualan. berisi,

• kode_transaksi : kode transaksi yang dilakukan oleh pelanggan/customer
• kode_pelanggan : kode setiap pelanggan yang bertransaksi
• tanggal_transaksi : waktu dimana transaksi dilakukan

4. tr_penjualan_detail berisi tentang transaksi penjualan secara detail. berisi,

• kode_transaksi : kode transaksi yang dilakukan oleh pelanggan/customer
• kode_produk : kode yang tertera pada produk
• qty : jumlah kuantitas produk yang tersedia
• harga_satuan : harga per produk/kuantitas

Project Task and Answers

1. Produk DQLab Mart

tampilkan daftar produk yang memiliki harga antara 50.000 and 150.000.

SELECT * FROM ms_produk WHERE harga BETWEEN 50000 AND 150000

• SELECT semua isi yang ada pada tabel ms_produk dengan tanda *
• Lalu filter menggunakan WHERE BETWEEN karena kita disini disuruh untuk mencari harga di antara 5000 dan 150000

hasilnya :

2. Thumb drive di DQLab Mart

Tampilkan semua produk yang mengandung kata Flashdisk.

SELECT * FROM ms_produk WHERE nama_produk LIKE 'Flashdisk%'

• SELECT semua isi yang ada pada tabel ms_produk dengan tanda *
• Filter menggunakan WHERE LIKE karena kita ingin mencari produk yang memiliki nama Flashdisk dengan ‘Flashdisk%’ tanda % dibelakang untuk melanjutkan setelah kata Flashdisk

hasilnya :

3. Pelanggan Bergelar

Tampilkan hanya nama-nama pelanggan yang hanya memiliki gelar-gelar berikut: S.H, Ir. dan Drs.

SELECT 
  no_urut, kode_pelanggan, nama_pelanggan, alamat 
FROM 
  ms_pelanggan 
WHERE 
 nama_pelanggan like "%S.H." or
 nama_pelanggan like "Ir.%" or
 nama_pelanggan like "%Drs."

• Filter menggunakan WHERE LIKE karena kita ingin mencari nama yang memiliki yang memiliki gelar saja dengan tanda % dibelakang atau depan kata gelar agar kata sesudah/sebelumnya dapat disertakan

hasilnya :

4. Mengurutkan Nama Pelanggan

Tampilkan nama-nama pelanggan dan urutkan hasilnya berdasarkan kolom nama_pelanggan dari yang terkecil ke yang terbesar (A ke Z).

SELECT nama_pelanggan FROM ms_pelanggan ORDER BY nama_pelanggan asc

• Disini kita hanya perlu mengurutkan nama sesuai abjad dengan order by ascending

hasilnya :

5. Mengurutkan Nama Pelanggan Tanpa Gelar

Tampilkan nama-nama pelanggan dan urutkan hasilnya berdasarkan kolom nama_pelanggan dari yang terkecil ke yang terbesar (A ke Z), namun gelar tidak boleh menjadi bagian dari urutan. Contoh: Ir. Agus Nugraha harus berada di atas Heidi Goh.

SELECT
 nama_pelanggan
FROM 
 ms_pelanggan
ORDER BY
 CASE WHEN LEFT(nama_pelanggan,3) = 'Ir.' THEN substring(nama_pelanggan,5,100) ELSE nama_pelanggan END asc;

• Disini kita hanya perlu mengurutkan berdasarkan CASE dengan mengambil gelar Agus Nugraha di kiri namanya dengan menggunakan left(nama_pelanggan,3) lalu SUBSTRING karakter namanya ke-5 dan ELSE berdasarkan nama_pelanggan

hasilnya :

6. Nama Pelanggan yang Paling Panjang

Tampilkan nama pelanggan yang memiliki nama paling panjang. Jika ada lebih dari 1 orang yang memiliki panjang nama yang sama, tampilkan semuanya.

SELECT
 *
FROM ( 
  SELECT nama_pelanggan 
  FROM ms_pelanggan 
ORDER BY length(nama_pelanggan) desc,nama_pelanggan) as a
LIMIT 1

• Disini kita menggunakan subquery dan filter menggunakan ORDER BY length(nama_pelanggan) desc karena ingin memilih karakter nama yang paling panjang dengan DESC, serta diurutkan dengan urutan alfabet. dan limit 1 karena yang paling panjang hanya 1 nama orang

hasilnya :

7. Nama Pelanggan yang Paling Panjang dengan Gelar

Tampilkan nama orang yang memiliki nama paling panjang (pada row atas), dan nama orang paling pendek (pada row setelahnya). Gelar menjadi bagian dari nama. Jika ada lebih dari satu nama yang paling panjang atau paling pendek, harus ditampilkan semuanya.

SELECT 
 *
FROM
 (
   SELECT
    nama_pelanggan
   FROM
    ms_pelanggan
   ORDER BY length(nama_pelanggan) desc,nama_pelanggan limit 1) as a
UNION
SELECT 
 *
FROM
 (
   SELECT
    nama_pelanggan
   FROM
    ms_pelanggan
   ORDER BY length(nama_pelanggan),nama_pelanggan limit 1) as b

• Disini kuncinya kita hanya perlu membuat dua parameter yang berbeda dan digabung menjadi satu dengan UNION
• Untuk tabel yang atas kita perlu membuat subquery sebagai kondisi dengan mengurutkan menggunakan ORDER BY paling panjang dengan LENGTHlength(nama_pelanggan) desc,nama_pelanggan limit 1 karena hanya ada 1 nama
• Untuk tabel yang atas kita juga perlu membuat subquery sebagai kondisi dengan mengurutkan menggunakan ORDER BY paling pendek denganlength(nama_pelanggan) asc,nama_pelanggan limit 1 karena hanya ada 1 nama

hasilnya :

8. Kuantitas Produk yang Banyak Terjual

Tampilkan produk yang paling banyak terjual dari segi kuantitas. Jika ada lebih dari 1 produk dengan nilai yang sama, tampilkan semua produk tersebut.

SELECT
  ms_produk.kode_produk, 
  ms_produk.nama_produk,
  sum(tr_penjualan_detail.qty) as total_qty
FROM 
  ms_produk
JOIN
  tr_penjualan_detail 
ON ms_produk.kode_produk = tr_penjualan_detail.kode_produk
GROUP BY
  ms_produk.kode_produk, 
  ms_produk.nama_produk
HAVING 
  total_qty=7

• Pada SELECT kita perlu menjumlahkan total_qty dengan SUM
• Serta JOIN tr_penjualan_detail dan key indicator yaitu kode_produk pada fungsi ON
• Lalu kelompokkan berdasarkan nama_produk dan Kode_produk pada GROUP BY
• Setelah gunakanlah fungsi HAVING sebagai filter karena total_qty merupakan perhitungan sama dengan 7 karena total kuantitas paling banyak

hasilnya :

9. Pelanggan Paling Tinggi Nilai Belanjanya

Siapa saja pelanggan yang paling banyak menghabiskan uangnya untuk belanja? Jika ada lebih dari 1 pelanggan dengan nilai yang sama, tampilkan semua pelanggan tersebut.

SELECT
  a.kode_pelanggan,
  a.nama_pelanggan,
  sum(c.harga_satuan*c.qty) as total_harga
FROM
  ms_pelanggan a
 JOIN tr_penjualan b ON a.kode_pelanggan=b.kode_pelanggan
 JOIN tr_penjualan_detail c ON b.kode_transaksi=c.kode_transaksi
GROUP BY 
  a.kode_pelanggan,
  a.nama_pelanggan
ORDER BY total_harga desc limit 1

• Pada SELECT kita perlu menjumlahkan antara harga_satuan dan qty untuk memperoleh total_harga
• Selanjutnya gabungkan tabel tr_penjualan dan tr_penjualan_detail dengan fungsi JOIN dengan key indicator kode_pelanggan dan kode_transaksi pada ON
• Setelah itu kelompokkan berdasarkan kode_pelanggan dan nama_pelanggan
• Lalu urutkan dengan menggunakan yang tertinggi ORDER BY DESC dengan limit 1 karena yang memiliki transaksi paling tinggi hanya 1 orang

hasilnya :

right -> final result

10. Pelanggan yang Belum Pernah Berbelanja

SELECT
  a.kode_pelanggan,
  a.nama_pelanggan,
  a.alamat
FROM 
  ms_pelanggan a
WHERE 
  a.kode_pelanggan NOT IN (SELECT kode_pelanggan FROM tr_penjualan)

• Disini kita hanya perlu filter kode_pelanggan yang tidak pernah bertransaksi dengan fungsi NOT IN, lalu SELECT berdasarkan pada kode_pelanggan dari tabel tr_penjualan

hasilnya :

11. Transaksi Belanja dengan Daftar Belanja lebih dari 1

Tampilkan transaksi-transaksi yang memiliki jumlah item produk lebih dari 1 jenis produk. Dengan lain kalimat, tampilkan transaksi-transaksi yang memiliki jumlah baris data pada table tr_penjualan_detail lebih dari satu.

SELECT
  a.kode_transaksi,
  a.kode_pelanggan,
  b.nama_pelanggan,
  a.tanggal_transaksi,
  count(c.kode_produk) as jumlah_detail
FROM
  tr_penjualan a
JOIN ms_pelanggan b ON a.kode_pelanggan=b.kode_pelanggan 
JOIN tr_penjualan_detail c ON a.kode_transaksi=c.kode_transaksi
GROUP BY 
  a.kode_transaksi,
  a.kode_pelanggan,
  b.nama_pelanggan,
  a.tanggal_transaksi
HAVING count(c.kode_produk)>1

• Pada fungsi SELECT kita harus menghitung kemunculan jumlah_detail pada kode_produk dengan menggunakan COUNT
• Selanjutnya JOIN tabel ms_pelanggan dan tr_penjualan detail dengan key indicator kode_pelanggan dan kode_transaksi
• Lalu kelompokkan berdasarkan kode_transaksi, kode_pelanggan, nama_pelanggan, tanggal_transaksi. jangan lupa untuk filter count(c.kode_produk) dengan kumunculan jumlah_detail >1 pada fungsi HAVING

hasilnya :

Alhamdulillah untuk Challenge SQL dari DQLab kali ini sudah selesai. Terimakasih yang sudah menyimak semoga ilmu dan manfaat kembali kepada kita semua…