1. urutan langkah-langkah dari pertama sampai selesai project dengan SQL bagi seorang Data Analyst
Minggu 1-2: Dasar-Dasar SQL
BULAN 2.
Minggu 1-2: SQL Lanjut dan Pengolahan Data
Tips Tambahan:
Jadwal ini fokus pada pemahaman cepat konsep inti dan aplikasi praktis, mempersiapkanmu untuk peran data analyst dengan keterampilan yang dibutuhkan.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
EXERCISE:
Berikut adalah contoh query dasar untuk mengambil, memfilter, dan menggabungkan data dari beberapa tabel. Misalkan kita memiliki tiga tabel berikut:
Tabel customers:
customer_id (Primary Key)
first_name
last_name
emailSyntax:
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
email VARCHAR(100)
);INSERT INTO customers (first_name, last_name, email) VALUES
('John', 'Doe', 'john.doe@example.com'),
('Jane', 'Smith', 'jane.smith@example.com'),
('Michael', 'Johnson', 'michael.johnson@example.com'),
('Emily', 'Davis', 'emily.davis@example.com'),
('Daniel', 'Brown', 'daniel.brown@example.com'),
('Sophia', 'Miller', 'sophia.miller@example.com'),
('James', 'Wilson', 'james.wilson@example.com'),
('Olivia', 'Moore', 'olivia.moore@example.com'),
('William', 'Taylor', 'william.taylor@example.com'),
('Ava', 'Anderson', 'ava.anderson@example.com'),
('Lucas', 'Thomas', 'lucas.thomas@example.com'),
('Isabella', 'Jackson', 'isabella.jackson@example.com'),
('Mason', 'White', 'mason.white@example.com'),
('Mia', 'Harris', 'mia.harris@example.com'),
('Ethan', 'Martin', 'ethan.martin@example.com'),
('Charlotte', 'Thompson', 'charlotte.thompson@example.com'),
('Benjamin', 'Garcia', 'benjamin.garcia@example.com'),
('Amelia', 'Martinez', 'amelia.martinez@example.com'),
('Elijah', 'Robinson', 'elijah.robinson@example.com'),
('Harper', 'Clark', 'harper.clark@example.com');2. Tabel orders:
order_id (Primary Key)
customer_id (Foreign Key dari customers)
order_dateSyntax:
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INT REFERENCES users(id),
order_date DATE
);INSERT INTO orders (customer_id, order_date) VALUES
(1, '2024-10-01'),
(2, '2024-10-02'),
(3, '2024-10-03'),
(4, '2024-10-04'),
(5, '2024-10-05'),
(6, '2024-10-06'),
(7, '2024-10-07'),
(8, '2024-10-08'),
(9, '2024-10-09'),
(10, '2024-10-10'),
(11, '2024-10-11'),
(12, '2024-10-12'),
(13, '2024-10-13'),
(14, '2024-10-14'),
(15, '2024-10-15'),
(16, '2024-10-16'),
(17, '2024-10-17'),
(18, '2024-10-18'),
(19, '2024-10-19'),
(20, '2024-10-20');3. Tabel order_items:
order_item_id (Primary Key)
order_id (Foreign Key dari orders)
product_name
quantity
price_per_unitSyntax:
CREATE TABLE order_items (
order_item_id SERIAL PRIMARY KEY,
order_id INT REFERENCES orders(order_id),
product_name VARCHAR(100),
quantity INT,
price_per_unit DECIMAL(10, 2)
);INSERT INTO order_items (order_id, product_name, quantity, price_per_unit) VALUES
(1, 'Laptop', 1, 1200.50),
(2, 'Smartphone', 2, 800.00),
(3, 'Tablet', 1, 400.00),
(4, 'Headphones', 3, 150.00),
(5, 'Keyboard', 2, 75.00),
(6, 'Monitor', 1, 300.00),
(7, 'Mouse', 2, 50.00),
(8, 'Printer', 1, 200.00),
(9, 'Camera', 1, 500.00),
(10, 'Smartwatch', 1, 250.00),
(11, 'External Hard Drive', 2, 100.00),
(12, 'Router', 1, 120.00),
(13, 'Webcam', 3, 70.00),
(14, 'Graphics Card', 1, 600.00),
(15, 'Desk Lamp', 1, 45.00),
(16, 'Chair', 1, 150.00),
(17, 'Backpack', 1, 90.00),
(18, 'Speakers', 2, 175.00),
(19, 'Microphone', 1, 130.00),
(20, 'VR Headset', 1, 350.00);
1. Mengambil Data dari Satu Tabel (SELECT):
sqlSELECT first_name, last_name, email
FROM customers;
Penjelasan: Mengambil kolom first_name
, last_name
, dan email
dari tabel customers
.
2. Memfilter Data (WHERE):
sqlSELECT first_name, last_name, email
FROM customers
WHERE email LIKE '%gmail.com';
Penjelasan: Mengambil pelanggan yang memiliki email dengan domain gmail.com
.
3. Menggabungkan Data dari Dua Tabel (JOIN):
sqlSELECT customers.first_name, customers.last_name, orders.order_date
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id;
Penjelasan: Query ini menggabungkan tabel customers
dan orders
berdasarkan customer_id
. Mengambil first_name
, last_name
, dan order_date
dari hasil gabungan kedua tabel.
4. Menggabungkan Data dari Tiga Tabel (Multiple JOINs):
sqlSELECT
customers.first_name,
customers.last_name,
orders.order_date,
order_items.product_name,
order_items.quantity,
order_items.price_per_unit
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id;
- Penjelasan: Menggabungkan tiga tabel (
customers
,orders
, danorder_items
) untuk mendapatkan informasi pelanggan, pesanan, dan detail produk yang dibeli dalam satu query.
5. Memfilter Setelah JOIN:
sqlSELECT
customers.first_name,
customers.last_name,
orders.order_date,
order_items.product_name,
order_items.quantity,
order_items.price_per_unit
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
JOIN order_items ON orders.order_id = order_items.order_id
WHERE orders.order_date >= '2023-01-01'
AND order_items.quantity > 1;
- Penjelasan: Menambahkan kondisi
WHERE
untuk memfilter pesanan yang dilakukan sejak 1 Januari 2023 dan hanya menampilkan produk yang jumlah kuantitasnya lebih dari 1.
6. Mengelompokkan dan Menghitung Data (GROUP BY dan COUNT):
sqlSELECT customers.customer_id, COUNT(orders.order_id) AS total_orders
FROM customers
JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY customers.customer_id;
- Penjelasan: Query ini menghitung jumlah pesanan (
COUNT(orders.order_id)
) dari setiap pelanggan (customer_id
) menggunakanGROUP BY
.
Rangkuman:
- SELECT: Untuk memilih kolom yang ingin ditampilkan.
- WHERE: Untuk memfilter hasil berdasarkan kondisi tertentu.
- JOIN: Untuk menggabungkan data dari beberapa tabel.
- GROUP BY: Untuk mengelompokkan data berdasarkan kolom tertentu dan melakukan agregasi, seperti
COUNT
,SUM
, dll.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
1. Kalkulator Sederhana
Program ini menerima input dari pengguna dan melakukan operasi aritmatika dasar (penjumlahan, pengurangan, perkalian, dan pembagian).
pythondef calculator():
print("=== Kalkulator Sederhana ===")
# Input dari pengguna
num1 = float(input("Masukkan angka pertama: "))
operator = input("Masukkan operator (+, -, *, /): ")
num2 = float(input("Masukkan angka kedua: "))
# Operasi Aritmatika
if operator == "+":
result = num1 + num2
elif operator == "-":
result = num1 - num2
elif operator == "*":
result = num1 * num2
elif operator == "/":
if num2 != 0:
result = num1 / num2
else:
return "Tidak bisa membagi dengan nol!"
else:
return "Operator tidak valid!"
return f"Hasil: {result}"
print(calculator())
Penjelasan: Program ini meminta dua angka dari pengguna dan operator yang akan digunakan, kemudian menghitung hasilnya berdasarkan operator tersebut.
2. Pengolahan List
Program ini melakukan berbagai operasi pada list, seperti menambahkan elemen, menghapus elemen, dan mencetak elemen.
pythondef list_operations():
print("=== Pengolahan List ===")
# Inisialisasi list
my_list = []
while True:
print("\nPilih Operasi:")
print("1. Tambah Elemen")
print("2. Hapus Elemen")
print("3. Lihat List")
print("4. Keluar")
choice = input("Masukkan pilihan: ")
if choice == "1":
element = input("Masukkan elemen yang ingin ditambahkan: ")
my_list.append(element)
print(f"{element} telah ditambahkan.")
elif choice == "2":
element = input("Masukkan elemen yang ingin dihapus: ")
if element in my_list:
my_list.remove(element)
print(f"{element} telah dihapus.")
else:
print(f"{element} tidak ditemukan di list.")
elif choice == "3":
print("Isi List:", my_list)
elif choice == "4":
print("Keluar dari program.")
break
else:
print("Pilihan tidak valid!")
list_operations()
Penjelasan: Program ini mengizinkan pengguna untuk menambah, menghapus, dan melihat elemen dari list. Pengguna bisa terus melakukan operasi sampai memilih untuk keluar dari program.
3. Manipulasi File Teks
Program ini membaca dan menulis ke file teks. Pengguna bisa menambahkan teks ke file, membaca isinya, atau mengganti konten file.
pythondef file_manipulation():
print("=== Manipulasi File Teks ===")
while True:
print("\nPilih Operasi:")
print("1. Tambah Teks ke File")
print("2. Baca Isi File")
print("3. Ganti Isi File")
print("4. Keluar")
choice = input("Masukkan pilihan: ")
file_name = "data.txt" # Nama file yang akan dimanipulasi
if choice == "1":
text = input("Masukkan teks yang ingin ditambahkan: ")
with open(file_name, "a") as file: # Mode "a" untuk menambahkan teks
file.write(text + "\n")
print("Teks telah ditambahkan ke file.")
elif choice == "2":
try:
with open(file_name, "r") as file: # Mode "r" untuk membaca file
print("Isi File:")
print(file.read())
except FileNotFoundError:
print("File tidak ditemukan!")
elif choice == "3":
text = input("Masukkan teks baru untuk menggantikan isi file: ")
with open(file_name, "w") as file: # Mode "w" untuk menimpa isi file
file.write(text + "\n")
print("Isi file telah diganti.")
elif choice == "4":
print("Keluar dari program.")
break
else:
print("Pilihan tidak valid!")
file_manipulation()
Penjelasan: Program ini memungkinkan pengguna untuk menambah teks ke dalam file, membaca isi file, atau mengganti isi file. Jika file belum ada, program akan membuatnya.
Dengan tiga contoh ini, Anda dapat belajar dasar-dasar operasi aritmatika, manipulasi list, dan pengelolaan file teks dalam Python.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Untuk membuat analisis dengan query kompleks menggunakan dataset, kita akan mengambil contoh dataset dari Kaggle. Misalkan dataset yang digunakan adalah "Superstore Sales" yang mencakup data penjualan produk dari sebuah toko retail besar. Dataset ini memiliki tabel-tabel seperti orders, customers, products, dan regions.
Pertanyaan bisnis yang akan dijawab:
- Produk apa yang memiliki penjualan terbanyak di tahun 2023?
- Berapa total pendapatan yang diperoleh dari setiap region pada kuartal pertama tahun 2023?
- Siapa pelanggan yang paling banyak melakukan pembelian, dan berapa total yang telah mereka habiskan?
- Berapa jumlah produk yang terjual di setiap kategori selama periode tertentu?
- Apakah ada pola di mana region tertentu lebih banyak berkontribusi pada kategori produk tertentu?
Langkah-langkah Analisis dengan Query SQL
Misalkan tabel-tabel di dataset ini terdiri dari:
orders
: Berisi detail pesanan.order_id
,order_date
,customer_id
,region_id
,total_amount
order_items
: Berisi detail produk di setiap pesanan.order_item_id
,order_id
,product_id
,quantity
,price_per_unit
products
: Berisi informasi produk.product_id
,category
,product_name
customers
: Berisi informasi pelanggan.customer_id
,first_name
,last_name
regions
: Berisi informasi region atau wilayah.region_id
,region_name
Query 1: Produk dengan Penjualan Terbanyak di Tahun 2023
sqlSELECT p.product_name, SUM(oi.quantity) AS total_quantity_sold
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE YEAR(o.order_date) = 2023
GROUP BY p.product_name
ORDER BY total_quantity_sold DESC
LIMIT 1;
- Penjelasan: Query ini menghitung jumlah produk yang terjual (
SUM(oi.quantity)
) di tahun 2023 untuk setiap produk. Hasilnya diurutkan dari penjualan terbanyak hingga terkecil, dan hanya produk dengan penjualan tertinggi yang ditampilkan.
Query 2: Total Pendapatan per Region di Kuartal Pertama Tahun 2023
sqlSELECT r.region_name, SUM(o.total_amount) AS total_revenue
FROM orders o
JOIN regions r ON o.region_id = r.region_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY r.region_name
ORDER BY total_revenue DESC;
- Penjelasan: Query ini menghitung total pendapatan (
SUM(o.total_amount)
) dari setiap region selama kuartal pertama tahun 2023 (Januari hingga Maret). Hasilnya diurutkan dari yang tertinggi ke yang terendah.
Query 3: Pelanggan dengan Total Pengeluaran Terbanyak
sqlSELECT c.first_name, c.last_name, SUM(o.total_amount) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.first_name, c.last_name
ORDER BY total_spent DESC
LIMIT 1;
- Penjelasan: Query ini menghitung total pengeluaran (
SUM(o.total_amount)
) dari setiap pelanggan berdasarkan pesanan yang telah mereka lakukan. Pelanggan dengan pengeluaran terbanyak akan ditampilkan.
Query 4: Jumlah Produk Terjual per Kategori Selama Periode Tertentu
Misalnya periode yang diinginkan adalah dari April 2023 hingga Juni 2023 (kuartal kedua).
sqlSELECT p.category, SUM(oi.quantity) AS total_quantity_sold
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-04-01' AND '2023-06-30'
GROUP BY p.category
ORDER BY total_quantity_sold DESC;
- Penjelasan: Query ini menghitung jumlah produk yang terjual (
SUM(oi.quantity)
) untuk setiap kategori selama periode April hingga Juni 2023. Data diurutkan dari kategori dengan penjualan tertinggi.
Query 5: Pola Penjualan Kategori Produk Berdasarkan Region
sqlSELECT r.region_name, p.category, SUM(oi.quantity) AS total_quantity_sold
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN regions r ON o.region_id = r.region_id
GROUP BY r.region_name, p.category
ORDER BY r.region_name, total_quantity_sold DESC;
- Penjelasan: Query ini menampilkan pola penjualan berdasarkan region dan kategori produk. Hasilnya diurutkan berdasarkan region, dan di setiap region ditampilkan kategori produk dengan jumlah penjualan tertinggi terlebih dahulu.
Rangkuman:
Dengan menggunakan query-query SQL yang lebih kompleks, kita dapat menjawab berbagai pertanyaan bisnis seperti:
- Produk dengan penjualan terbanyak.
- Pendapatan berdasarkan region.
- Pelanggan dengan pengeluaran tertinggi.
- Penjualan produk per kategori selama periode tertentu.
- Pola penjualan kategori produk di setiap region.
Analisis ini membantu dalam pengambilan keputusan bisnis berdasarkan data, seperti menargetkan pelanggan tertentu, merancang strategi penjualan per region, atau mengetahui produk-produk yang paling laris.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Untuk menerapkan teknik pengolahan data menggunakan Pandas, pertama kita membutuhkan dataset dalam format CSV atau Excel. Mari kita ambil contoh sederhana dataset penjualan yang bernama sales_data.csv. Dataset ini berisi data penjualan harian dengan kolom-kolom berikut:
order_id
: ID Pesananorder_date
: Tanggal Pesanancustomer_id
: ID Pelangganproduct
: Nama Produkcategory
: Kategori Produkquantity
: Jumlah Produk Terjualprice_per_unit
: Harga per Unittotal_amount
: Total Harga Pesanan
1. Membaca Dataset CSV Menggunakan Pandas
pythonimport pandas as pd
# Membaca file CSV
df = pd.read_csv('sales_data.csv')
# Menampilkan 5 baris pertama dataset
print(df.head())
Dengan pd.read_csv()
, kita dapat membaca file CSV dan melihat 5 baris pertama menggunakan head()
.
2. Memeriksa Informasi Data
python# Menampilkan informasi tentang dataset
print(df.info())
# Melihat deskripsi statistik dataset
print(df.describe())
Dengan info()
, kita dapat melihat tipe data setiap kolom dan ada tidaknya nilai yang hilang (missing values). describe()
memberikan ringkasan statistik seperti mean, standard deviation, min, dan max untuk kolom numerik.
3. Menghitung Total Pendapatan (Total Sales)
Kita dapat membuat kolom baru yang berisi total pendapatan dari setiap pesanan dengan mengalikan jumlah produk dengan harga per unit.
python# Membuat kolom baru 'total_sales' dengan menghitung quantity * price_per_unit
df['total_sales'] = df['quantity'] * df['price_per_unit']
# Menampilkan beberapa baris dengan kolom baru
print(df[['order_id', 'product', 'quantity', 'price_per_unit', 'total_sales']].head())
4. Memfilter Data Berdasarkan Tanggal dan Kategori Produk
Misalkan kita ingin memfilter semua penjualan produk dalam kategori "Electronics" yang terjadi setelah 1 Januari 2023.
python# Mengonversi kolom 'order_date' menjadi tipe datetime
df['order_date'] = pd.to_datetime(df['order_date'])
# Memfilter data
filtered_df = df[(df['order_date'] > '2023-01-01') & (df['category'] == 'Electronics')]
# Menampilkan hasil filter
print(filtered_df.head())
5. Menghitung Total Penjualan per Kategori
Untuk menghitung total penjualan berdasarkan kategori produk:
python# Mengelompokkan data berdasarkan kategori dan menghitung total penjualan
sales_per_category = df.groupby('category')['total_sales'].sum().reset_index()
# Menampilkan total penjualan per kategori
print(sales_per_category)
6. Mengidentifikasi Pelanggan dengan Pembelian Terbesar
Kita dapat mencari pelanggan yang melakukan pembelian terbesar dengan mengelompokkan berdasarkan customer_id
.
python# Mengelompokkan berdasarkan customer_id dan menghitung total pembelian
top_customers = df.groupby('customer_id')['total_sales'].sum().reset_index()
# Mengurutkan pelanggan berdasarkan total pembelian secara descending
top_customers = top_customers.sort_values(by='total_sales', ascending=False)
# Menampilkan 5 pelanggan dengan pembelian terbesar
print(top_customers.head())
7. Menggabungkan (Merge) dengan Dataset Lain
Misalkan kita memiliki dataset tambahan dengan informasi pelanggan (customer_data.csv
), kita dapat menggabungkannya dengan dataset penjualan.
python# Membaca dataset pelanggan
customers_df = pd.read_csv('customer_data.csv')
# Menggabungkan dataset penjualan dengan dataset pelanggan
merged_df = pd.merge(df, customers_df, on='customer_id', how='left')
# Menampilkan hasil penggabungan
print(merged_df.head())
8. Menulis Dataset Hasil Pengolahan ke File Baru
Setelah mengolah dataset, kita bisa menyimpannya kembali ke file CSV atau Excel.
python# Menyimpan hasil pengolahan ke file CSV baru
merged_df.to_csv('processed_sales_data.csv', index=False)
# Atau menyimpan ke file Excel
merged_df.to_excel('processed_sales_data.xlsx', index=False)
Rangkuman:
Dengan Pandas, kita bisa melakukan banyak teknik pengolahan data seperti:
- Membaca data dari file CSV atau Excel.
- Menambahkan kolom baru berdasarkan perhitungan.
- Memfilter data berdasarkan kriteria tertentu.
- Mengelompokkan data dan menghitung agregat (seperti total penjualan).
- Menggabungkan (merge) beberapa dataset.
- Menulis dataset hasil pengolahan ke file baru.
Pandas sangat kuat untuk analisis data dalam skala besar, termasuk dalam kasus penggunaan analisis bisnis dan laporan berbasis data.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Dataset:
- Orders: Data pesanan.
order_id
,order_date
,customer_id
,total_amount
- Order_Items: Data rincian item per pesanan.
order_item_id
,order_id
,product_id
,quantity
,price_per_unit
- Products: Data produk.
product_id
,category
,product_name
,unit_cost
- Customers: Data pelanggan.
customer_id
,first_name
,last_name
,email
- Regions: Data wilayah.
region_id
,region_name
Pertanyaan Bisnis:
- Siapa pelanggan yang menghasilkan penjualan tertinggi dalam kuartal pertama 2023?
- Berapa rata-rata total pesanan dan pesanan maksimum yang dilakukan oleh pelanggan pada periode yang sama?
- Produk apa yang memberikan margin keuntungan terbesar selama periode ini?
1. Menghitung Pelanggan dengan Penjualan Tertinggi (Total Sales per Customer)
Menggunakan join untuk menggabungkan beberapa tabel (Orders, Customers) dan GROUP BY untuk menghitung total penjualan per pelanggan.
sqlSELECT
c.first_name,
c.last_name,
SUM(o.total_amount) AS total_sales
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY c.first_name, c.last_name
ORDER BY total_sales DESC
LIMIT 10;
- Penjelasan: Query ini menggabungkan data dari tabel orders dan customers. Kita menghitung total penjualan (
SUM(o.total_amount)
) per pelanggan dalam kuartal pertama tahun 2023. Hasilnya diurutkan dari penjualan tertinggi.
2. Menghitung Rata-rata dan Maksimum Total Pesanan per Pelanggan dengan Window Function
Menggunakan window function untuk menghitung rata-rata dan pesanan maksimum per pelanggan selama kuartal pertama 2023.
sqlSELECT
o.customer_id,
c.first_name,
c.last_name,
o.order_id,
o.total_amount,
AVG(o.total_amount) OVER (PARTITION BY o.customer_id) AS avg_order_amount,
MAX(o.total_amount) OVER (PARTITION BY o.customer_id) AS max_order_amount
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-03-31';
- Penjelasan: Window functions digunakan untuk menghitung rata-rata (
AVG
) dan nilai maksimum (MAX
) dari total pesanan untuk setiap pelanggan. Alih-alih mengelompokkan data, window function memungkinkan kita untuk menghitung agregat tanpa mengubah tampilan baris data.
3. Menghitung Produk dengan Margin Keuntungan Terbesar
Untuk menghitung margin keuntungan per produk, kita perlu menggabungkan data dari order_items dan products dan menggunakan window function untuk menghitung margin serta mengurutkan berdasarkan keuntungan terbesar.
sqlSELECT
p.product_name,
SUM(oi.quantity * (oi.price_per_unit - p.unit_cost)) AS total_margin,
RANK() OVER (ORDER BY SUM(oi.quantity * (oi.price_per_unit - p.unit_cost)) DESC) AS rank
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY total_margin DESC
LIMIT 10;
- Penjelasan: Query ini menghitung margin keuntungan untuk setiap produk dengan rumus
(harga_jual - harga_beli) * jumlah_terjual
. Hasilnya diurutkan berdasarkan margin keuntungan terbesar, dan kita menggunakan window functionRANK()
untuk memberi peringkat pada produk.
4. Menghitung Penjualan per Region dengan Analisis Lebih Mendalam
Menggabungkan data dari orders dan regions untuk menghitung total penjualan berdasarkan region, kemudian melihat rata-rata penjualan per region dengan window function.
sqlSELECT
r.region_name,
SUM(o.total_amount) AS total_sales,
AVG(SUM(o.total_amount)) OVER () AS avg_sales,
SUM(o.total_amount) - AVG(SUM(o.total_amount)) OVER () AS sales_difference
FROM orders o
JOIN regions r ON o.region_id = r.region_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-03-31'
GROUP BY r.region_name
ORDER BY total_sales DESC;
- Penjelasan: Query ini menghitung total penjualan (
SUM(o.total_amount)
) untuk setiap region. Window functionAVG()
digunakan untuk menghitung rata-rata penjualan global, dan kita juga menghitung selisih antara penjualan total region dengan rata-rata global.
5. Menghitung Tren Penjualan Produk Berdasarkan Bulan
Menggunakan window function untuk menghitung tren bulanan berdasarkan kategori produk.
sqlWITH monthly_sales AS (
SELECT
p.category,
DATE_TRUNC('month', o.order_date) AS month,
SUM(oi.quantity * oi.price_per_unit) AS total_sales
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE o.order_date BETWEEN '2023-01-01' AND '2023-12-31'
GROUP BY p.category, month
)
SELECT
category,
month,
total_sales,
LAG(total_sales) OVER (PARTITION BY category ORDER BY month) AS previous_month_sales,
(total_sales - LAG(total_sales) OVER (PARTITION BY category ORDER BY month)) AS sales_change
FROM monthly_sales
ORDER BY category, month;
- Penjelasan: Query ini menghitung total penjualan bulanan per kategori produk, kemudian menggunakan
LAG()
untuk melihat perubahan penjualan dari bulan sebelumnya.LAG()
memungkinkan kita mengakses nilai dari baris sebelumnya dalam grup tertentu (dalam hal ini per kategori).
Rangkuman:
Dalam analisis dataset besar, penggunaan multiple table joins, window functions, dan teknik agregasi yang lebih dalam memungkinkan kita menjawab pertanyaan bisnis kompleks, seperti:
- Mengidentifikasi pelanggan atau produk dengan kontribusi tertinggi.
- Menghitung statistik seperti rata-rata dan nilai maksimum untuk setiap grup.
- Melihat tren dan perubahan dalam data dari waktu ke waktu.
- Menjalankan perhitungan agregat dalam konteks window tanpa mengelompokkan data secara tradisional.
Analisis ini membantu dalam mengidentifikasi pola bisnis, melakukan segmentasi pelanggan, dan memahami performa produk atau region secara lebih mendalam.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Berikut adalah langkah-langkah untuk membuat proyek yang menggabungkan SQL dan Python untuk menganalisis database dan memvisualisasikan hasil menggunakan Python (dengan Pandas dan Matplotlib/Seaborn).
Proyek: Analisis Penjualan dengan SQL dan Visualisasi dengan Python
1. Deskripsi Proyek
Proyek ini akan melakukan analisis penjualan menggunakan SQL untuk mendapatkan data dari database, kemudian menggunakan Python untuk memproses dan memvisualisasikan hasilnya. Analisis akan fokus pada:
- Total penjualan per produk.
- Penjualan bulanan per kategori produk.
- Pelanggan dengan kontribusi penjualan tertinggi.
2. Langkah-Langkah Proyek
- Langkah 1: Menghubungkan Python ke database menggunakan SQLAlchemy atau
sqlite3
. - Langkah 2: Mengambil data penjualan dari database menggunakan SQL.
- Langkah 3: Menganalisis data dengan Pandas.
- Langkah 4: Visualisasi data menggunakan Matplotlib dan Seaborn.
3. Tools dan Libraries yang Dibutuhkan
- SQL Database: Anda dapat menggunakan database SQLite atau MySQL.
- Python Libraries:
- SQLAlchemy atau sqlite3 untuk menghubungkan database.
- Pandas untuk manipulasi data.
- Matplotlib dan Seaborn untuk visualisasi data.
4. Struktur Database (Contoh)
Kita akan menggunakan tiga tabel:
- Orders: Menyimpan informasi tentang pesanan.
order_id
,order_date
,customer_id
,total_amount
- Order_Items: Menyimpan detail produk per pesanan.
order_item_id
,order_id
,product_id
,quantity
,price_per_unit
- Products: Menyimpan data produk.
product_id
,category
,product_name
,unit_cost
- Customers: Menyimpan data pelanggan.
customer_id
,first_name
,last_name
,email
5. Langkah 1: Koneksi ke Database dengan Python
Kita akan menggunakan SQLAlchemy untuk koneksi database.
pythonfrom sqlalchemy import create_engine
import pandas as pd
# Buat koneksi ke database SQLite
engine = create_engine('sqlite:///sales_database.db')
# Contoh query sederhana untuk mengambil data dari tabel
query = """
SELECT * FROM orders
"""
# Mengambil data menggunakan Pandas
orders_df = pd.read_sql(query, engine)
print(orders_df.head())
6. Langkah 2: Mengambil Data dari Multiple Tables (Join)
Kita akan mengambil data yang melibatkan penggabungan dari beberapa tabel, seperti Orders, Order_Items, dan Products.
pythonquery = """
SELECT
o.order_id,
o.order_date,
c.first_name,
c.last_name,
p.product_name,
oi.quantity,
oi.price_per_unit,
(oi.quantity * oi.price_per_unit) AS total_sales
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id
"""
# Mengambil data gabungan
sales_data = pd.read_sql(query, engine)
print(sales_data.head())
7. Langkah 3: Analisis Data dengan Pandas
Setelah data diambil, kita dapat melakukan analisis. Misalnya, kita ingin menghitung total penjualan per produk dan penjualan bulanan per kategori produk.
Total Penjualan per Produk:
python# Mengelompokkan berdasarkan produk untuk mendapatkan total penjualan
total_sales_per_product = sales_data.groupby('product_name')['total_sales'].sum().reset_index()
# Mengurutkan dari yang terbesar
total_sales_per_product = total_sales_per_product.sort_values(by='total_sales', ascending=False)
print(total_sales_per_product.head())
Penjualan Bulanan per Kategori Produk:
python# Mengubah kolom 'order_date' menjadi tipe datetime
sales_data['order_date'] = pd.to_datetime(sales_data['order_date'])
# Membuat kolom baru untuk bulan
sales_data['month'] = sales_data['order_date'].dt.to_period('M')
# Mengelompokkan berdasarkan kategori produk dan bulan
monthly_sales_per_category = sales_data.groupby(['month', 'product_name'])['total_sales'].sum().reset_index()
print(monthly_sales_per_category.head())
8. Langkah 4: Visualisasi Data dengan Matplotlib dan Seaborn
Sekarang, kita akan memvisualisasikan hasil analisis.
Visualisasi Total Penjualan per Produk:
pythonimport matplotlib.pyplot as plt
import seaborn as sns
# Membuat visualisasi total penjualan per produk
plt.figure(figsize=(10,6))
sns.barplot(x='total_sales', y='product_name', data=total_sales_per_product.head(10))
plt.title('Top 10 Produk Berdasarkan Penjualan Total')
plt.xlabel('Total Penjualan')
plt.ylabel('Nama Produk')
plt.show()
Visualisasi Penjualan Bulanan per Produk:
python# Membuat visualisasi penjualan bulanan per produk
plt.figure(figsize=(10,6))
sns.lineplot(x='month', y='total_sales', hue='product_name', data=monthly_sales_per_category)
plt.title('Penjualan Bulanan per Produk')
plt.xlabel('Bulan')
plt.ylabel('Total Penjualan')
plt.xticks(rotation=45)
plt.show()
9. Langkah 5: Menyimpan Hasil Analisis
Hasil analisis dapat disimpan ke dalam file CSV atau Excel untuk dibagikan atau digunakan lebih lanjut.
python# Menyimpan hasil total penjualan per produk ke CSV
total_sales_per_product.to_csv('total_sales_per_product.csv', index=False)
# Menyimpan hasil penjualan bulanan per kategori ke Excel
monthly_sales_per_category.to_excel('monthly_sales_per_category.xlsx', index=False)
Rangkuman:
Dalam proyek ini, kita menggabungkan SQL dan Python untuk:
- Mengambil data dari database menggunakan SQL dengan melakukan JOIN pada beberapa tabel.
- Mengolah data menggunakan Pandas untuk menghitung total penjualan, penjualan bulanan, dan pelanggan dengan kontribusi tertinggi.
- Visualisasi hasil analisis menggunakan Matplotlib dan Seaborn untuk memudahkan pemahaman data.
Proyek ini dapat diperluas dengan lebih banyak analisis seperti menghitung margin keuntungan, melakukan segmentasi pelanggan, atau membuat dashboard interaktif menggunakan Plotly atau Tableau.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Berikut adalah rencana proyek lengkap untuk menganalisis dataset bisnis, khususnya dalam konteks penjualan dan perilaku pelanggan. Proyek ini akan mencakup seluruh pipeline dari pengambilan data menggunakan SQL, pengolahan data menggunakan Python, hingga visualisasi dan penghasil insight.
Proyek: Analisis Penjualan dan Perilaku Pelanggan
1. Deskripsi Proyek
Proyek ini bertujuan untuk menganalisis data penjualan dan perilaku pelanggan dari sebuah database. Analisis ini akan mencakup:
- Total penjualan per produk.
- Analisis perilaku pelanggan berdasarkan frekuensi dan total pengeluaran.
- Visualisasi tren penjualan berdasarkan bulan.
2. Dataset yang Digunakan
Untuk proyek ini, kita akan menggunakan dataset yang berisi informasi berikut:
- Tabel Orders:
order_id
,order_date
,customer_id
,total_amount
- Tabel Order_Items:
order_item_id
,order_id
,product_id
,quantity
,price_per_unit
- Tabel Products:
product_id
,category
,product_name
,unit_cost
- Tabel Customers:
customer_id
,first_name
,last_name
,email
3. Pipeline Lengkap
Langkah 1: Pengambilan Data dengan SQL
Kita akan menggunakan SQL untuk menggabungkan tabel dan mengambil data yang dibutuhkan.
sql-- Query untuk mengambil data penjualan dan pelanggan
SELECT
o.order_id,
o.order_date,
c.customer_id,
c.first_name,
c.last_name,
oi.quantity,
oi.price_per_unit,
(oi.quantity * oi.price_per_unit) AS total_sales,
p.product_name,
p.category
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id;
Langkah 2: Menghubungkan Database dengan Python
Kita akan menggunakan SQLAlchemy untuk menghubungkan Python dengan database.
pythonfrom sqlalchemy import create_engine
import pandas as pd
# Buat koneksi ke database SQLite
engine = create_engine('sqlite:///sales_database.db')
# Mengambil data menggunakan query SQL
query = """
SELECT
o.order_id,
o.order_date,
c.customer_id,
c.first_name,
c.last_name,
oi.quantity,
oi.price_per_unit,
(oi.quantity * oi.price_per_unit) AS total_sales,
p.product_name,
p.category
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
JOIN customers c ON o.customer_id = c.customer_id;
"""
# Mengambil data ke dalam DataFrame
sales_data = pd.read_sql(query, engine)
print(sales_data.head())
Langkah 3: Mengolah Data dengan Python
Setelah data diambil, kita akan melakukan analisis menggunakan Pandas.
Total Penjualan per Produk
python# Menghitung total penjualan per produk
total_sales_per_product = sales_data.groupby('product_name')['total_sales'].sum().reset_index()
total_sales_per_product = total_sales_per_product.sort_values(by='total_sales', ascending=False)
print(total_sales_per_product.head())
Analisis Perilaku Pelanggan
python# Menghitung total pengeluaran dan frekuensi pembelian per pelanggan
customer_behavior = sales_data.groupby('customer_id').agg(
total_spent=('total_sales', 'sum'),
purchase_count=('order_id', 'count')
).reset_index()
# Menambahkan nama pelanggan untuk kemudahan analisis
customer_behavior = customer_behavior.merge(sales_data[['customer_id', 'first_name', 'last_name']].drop_duplicates(), on='customer_id')
print(customer_behavior.head())
Menambahkan Kolom Bulan untuk Analisis Tren
python# Mengubah 'order_date' menjadi datetime
sales_data['order_date'] = pd.to_datetime(sales_data['order_date'])
# Menambahkan kolom bulan
sales_data['month'] = sales_data['order_date'].dt.to_period('M')
# Menghitung penjualan bulanan
monthly_sales = sales_data.groupby(['month', 'product_name'])['total_sales'].sum().reset_index()
print(monthly_sales.head())
Langkah 4: Visualisasi Hasil
Kita akan menggunakan Matplotlib dan Seaborn untuk visualisasi data.
Visualisasi Total Penjualan per Produk
pythonimport matplotlib.pyplot as plt
import seaborn as sns
# Visualisasi total penjualan per produk
plt.figure(figsize=(10,6))
sns.barplot(x='total_sales', y='product_name', data=total_sales_per_product.head(10))
plt.title('Top 10 Produk Berdasarkan Total Penjualan')
plt.xlabel('Total Penjualan')
plt.ylabel('Nama Produk')
plt.show()
Visualisasi Analisis Perilaku Pelanggan
python# Visualisasi total pengeluaran pelanggan
plt.figure(figsize=(10,6))
sns.scatterplot(x='purchase_count', y='total_spent', data=customer_behavior)
plt.title('Pengeluaran Pelanggan vs. Frekuensi Pembelian')
plt.xlabel('Frekuensi Pembelian')
plt.ylabel('Total Pengeluaran')
plt.axhline(y=customer_behavior['total_spent'].mean(), color='r', linestyle='--', label='Rata-rata Pengeluaran')
plt.legend()
plt.show()
Visualisasi Tren Penjualan Bulanan
python# Visualisasi penjualan bulanan
plt.figure(figsize=(12,6))
sns.lineplot(x='month', y='total_sales', hue='product_name', data=monthly_sales)
plt.title('Tren Penjualan Bulanan per Produk')
plt.xlabel('Bulan')
plt.ylabel('Total Penjualan')
plt.xticks(rotation=45)
plt.show()
4. Insight yang Dapat Dihasilkan
Dari analisis yang dilakukan, kita bisa mendapatkan beberapa insight, seperti:
- Produk Terlaris: Mengetahui produk mana yang memberikan kontribusi terbesar terhadap total penjualan.
- Perilaku Pelanggan: Memahami hubungan antara total pengeluaran dan frekuensi pembelian pelanggan. Pelanggan yang lebih sering membeli mungkin juga mengeluarkan lebih banyak.
- Tren Penjualan: Mengidentifikasi pola penjualan dari waktu ke waktu, seperti musiman atau lonjakan penjualan selama periode tertentu.
5. Menyimpan Hasil Analisis
Hasil analisis dapat disimpan ke dalam file CSV untuk dibagikan atau digunakan lebih lanjut.
python# Menyimpan hasil total penjualan per produk
total_sales_per_product.to_csv('total_sales_per_product.csv', index=False)
# Menyimpan hasil analisis perilaku pelanggan
customer_behavior.to_csv('customer_behavior.csv', index=False)
# Menyimpan hasil penjualan bulanan
monthly_sales.to_csv('monthly_sales.csv', index=False)
Kesimpulan
Proyek ini mencakup seluruh pipeline dari pengambilan data menggunakan SQL hingga pengolahan dan visualisasi data dengan Python. Melalui analisis ini, Anda dapat menghasilkan insight yang berharga untuk memahami penjualan dan perilaku pelanggan, yang dapat digunakan untuk pengambilan keputusan bisnis yang lebih baik. Proyek ini dapat diperluas dengan lebih banyak analisis, seperti segmentasi pelanggan atau analisis prediktif.
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Answer:
atihan Harian (1-2 Jam Setiap Hari)
Hari Kerja (Senin - Jumat)
SQL Practice (30-60 Menit)
- Tujuan: Buat dan jalankan query untuk memahami dasar-dasar SQL, seperti SELECT, JOIN, dan WHERE.
- Aktivitas:
- Ambil dataset dari platform seperti Kaggle.
- Praktik dengan query dasar dan tingkatkan kesulitan secara bertahap.
- Cobalah untuk menyelesaikan 1-2 masalah SQL di LeetCode.
Python Practice (30-60 Menit)
- Tujuan: Belajar tentang pengolahan data, manipulasi list, dan fungsi dasar.
- Aktivitas:
- Cobalah membuat skrip sederhana, seperti kalkulator atau pengolahan list.
- Pelajari penggunaan Pandas untuk analisis data.
- Selesaikan 1-2 masalah Python di HackerRank.
Akhir Pekan (Sabtu - Minggu)
Review Kemajuan (1 Jam)
- Tinjau catatan yang telah Anda buat selama minggu ini.
- Identifikasi area yang perlu ditingkatkan.
- Catat pertanyaan atau tantangan yang Anda hadapi.
Coding Challenges (1-2 Jam)
SQL (LeetCode)
- Pilih 2-3 tantangan dari LeetCode yang berfokus pada SQL.
- Cobalah untuk menyelesaikan tantangan yang lebih sulit dari minggu sebelumnya.
Python (HackerRank)
- Pilih 2-3 tantangan dari HackerRank dengan tingkat kesulitan yang meningkat.
- Fokus pada penerapan konsep yang telah Anda pelajari selama seminggu.
Tips Tambahan
- Catat Kemajuan: Simpan catatan harian tentang apa yang telah Anda pelajari dan tantangan yang dihadapi.
- Diskusikan dengan Orang Lain: Bergabunglah dengan forum atau grup belajar untuk berdiskusi dan bertanya.
- Variasikan Materi: Cobalah untuk tidak hanya berfokus pada query atau pemrograman, tetapi juga pelajari konsep teori di baliknya.
Dengan rutinitas ini, Anda akan bisa meningkatkan keterampilan SQL dan Python Anda secara bertahap. Selamat belajar!
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- Get link
- X
- Other Apps
Labels
Data Analyst- Get link
- X
- Other Apps
Comments
Post a Comment