Data Analyst study with "prompt AI"

PROMPT AI:

SQL
1. urutan langkah-langkah dari pertama sampai selesai project dengan SQL bagi seorang Data Analyst

PYTHON

1. urutan langkah-langkah untuk melakukan data cleaning menggunakan Python

..........

Jadwal latihan 3 bulan untuk menguasai SQL dan Python dengan fokus menjadi Data Analyst.

Jadwal ini dirancang dengan intensitas tinggi untuk mempersingkat waktu belajar

Promp Latihan AI :

BULAN 1

Minggu 1-2: Dasar-Dasar SQL

>> Buat query dasar untuk mengambil, memfilter, dan menggabungkan data dari berbagai tabel.

Minggu 3-4: Dasar-Dasar Python

>> Buat program sederhana seperti kalkulator, pengolahan list, dan manipulasi file teks.

BULAN 2.

Minggu 1-2: SQL Lanjut dan Pengolahan Data

>> Ambil dataset (misalnya dari Kaggle), buat analisis dengan query kompleks untuk menjawab pertanyaan bisnis.

Minggu 3-4: Python untuk Analisis Data

>> Gunakan dataset (CSV atau Excel) dan terapkan teknik pengolahan data menggunakan Pandas.

BULAN 3

Minggu 1-2: SQL untuk Analisis Lanjutan

>> Analisis dataset besar dengan SQL, buat laporan yang melibatkan multiple table joins, window functions, dan analisis yang lebih dalam.

Minggu 3-4: Python untuk Visualisasi dan Proyek Gabungan

>> Buat proyek yang menggabungkan SQL dan Python, seperti menganalisis database dan memvisualisasikan hasilnya dengan Python.

Proyek Final (Minggu 3-4):

Pilih dataset bisnis (penjualan, customer behavior, dsb).

Buat pipeline lengkap dari SQL untuk mengambil data, Python untuk mengolah, dan hasilkan visualisasi serta insight.

Tips Tambahan:

Latihan Harian: Berlatih minimal 1-2 jam setiap hari untuk membuat query SQL dan skrip Python.

Akhir Pekan: Review kemajuan dan selesaikan coding challenges di platform seperti LeetCode (untuk SQL) atau HackerRank (untuk Python).

Jadwal ini fokus pada pemahaman cepat konsep inti dan aplikasi praktis, mempersiapkanmu untuk peran data analyst dengan keterampilan yang dibutuhkan.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

EXERCISE:

>> Buat query dasar untuk mengambil, memfilter, dan menggabungkan data dari berbagai tabel.

Answer:

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
email
Syntax:
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_date
Syntax:
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_unit
Syntax:
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):

sql
SELECT first_name, last_name, email
FROM customers;

Penjelasan: Mengambil kolom first_name, last_name, dan email dari tabel customers.

2. Memfilter Data (WHERE):

sql
SELECT 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):

sql
SELECT 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):

sql
SELECT 
    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, dan order_items) untuk mendapatkan informasi pelanggan, pesanan, dan detail produk yang dibeli dalam satu query.

5. Memfilter Setelah JOIN:

sql
SELECT 
    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):

sql
SELECT 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) menggunakan GROUP 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

>> Buat program sederhana seperti kalkulator, pengolahan list, dan manipulasi file teks.

Answer:

Berikut adalah tiga contoh program sederhana yang mencakup kalkulator, pengolahan list, dan manipulasi file teks menggunakan Python.

1. Kalkulator Sederhana

Program ini menerima input dari pengguna dan melakukan operasi aritmatika dasar (penjumlahan, pengurangan, perkalian, dan pembagian).

python
def 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.

python
def 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.

python
def 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

>> Ambil dataset (misalnya dari Kaggle), buat analisis dengan query kompleks untuk menjawab pertanyaan bisnis.

Answer:

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

sql
SELECT 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

sql
SELECT 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

sql
SELECT 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).

sql
SELECT 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

sql
SELECT 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

>> Gunakan dataset (CSV atau Excel) dan terapkan teknik pengolahan data menggunakan Pandas.

Answer:

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 Pesanan
order_date: Tanggal Pesanan
customer_id: ID Pelanggan
product: Nama Produk
category: Kategori Produk
quantity: Jumlah Produk Terjual
price_per_unit: Harga per Unit
total_amount: Total Harga Pesanan

1. Membaca Dataset CSV Menggunakan Pandas

python
import 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

>> Analisis dataset besar dengan SQL, buat laporan yang melibatkan multiple table joins, window functions, dan analisis yang lebih dalam.

Answer:

Untuk melakukan analisis dataset besar dengan SQL yang melibatkan multiple table joins, window functions, dan analisis yang lebih dalam, kita akan membahas sebuah skenario menggunakan dataset penjualan retail. Dalam skenario ini, kita memiliki beberapa tabel sebagai berikut:

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.

sql
SELECT 
    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.

sql
SELECT 
    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.

sql
SELECT 
    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 function RANK() 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.

sql
SELECT 
    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 function AVG() 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.

sql
WITH 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

>> Buat proyek yang menggabungkan SQL dan Python, seperti menganalisis database dan memvisualisasikan hasilnya dengan Python.

Answer:

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.

python
from 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.

python
query = """
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:

python
import 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

>> Proyek Final (Minggu 3-4):

Pilih dataset bisnis (penjualan, customer behavior, dsb).

Buat pipeline lengkap dari SQL untuk mengambil data, Python untuk mengolah, dan hasilkan visualisasi serta insight.

Answer:

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.

python
from 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

python
import 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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

>> TIPS TAMBAHAN

Latihan Harian: Berlatih minimal 1-2 jam setiap hari untuk membuat query SQL dan skrip Python.

Akhir Pekan: Review kemajuan dan selesaikan coding challenges di platform seperti LeetCode (untuk SQL) atau HackerRank (untuk Python).
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!

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

Story Julfahmi