(Disertai Penjelasan Lengkap)**
Studi kasus: Analisis penjualan toko online “MegaStore”.
Tujuannya: mempelajari proses data analyst dari nol → membuat database → query dasar → analisis → advanced query.
⭐ 1. Membuat Database
📝 Keterangan:
Database adalah wadah utama untuk menyimpan semua tabel.
✔ Query:
CREATE DATABASE megastore;
USE megastore;
📌 Hasil:
Database “megastore” berhasil dibuat dan dipilih untuk digunakan.
⭐ 2. Membuat Tabel
Kami butuh tiga tabel: customers, products, dan sales.
2.1. Tabel customers
📝 Keterangan:
Menyimpan data pelanggan.
customer_id→ Primary key (unique).name→ Nama pelanggan.city→ Kota pelanggan.join_date→ Tanggal bergabung.
✔ Query:
CREATE TABLE customers (
customer_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
city VARCHAR(50),
join_date DATE
);
2.2. Tabel products
📝 Keterangan:
Menyimpan daftar produk.
product_name→ Nama produkcategory→ Kategori (Elektronik, Aksesoris, dll)price→ Harga
✔ Query:
CREATE TABLE products (
product_id INT PRIMARY KEY AUTO_INCREMENT,
product_name VARCHAR(100),
category VARCHAR(50),
price DECIMAL(12,2)
);
2.3. Tabel sales
📝 Keterangan:
Menyimpan transaksi penjualan.
Memiliki foreign key karena setiap transaksi pasti terkait customer dan product.
✔ Query:
CREATE TABLE sales (
sale_id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT,
product_id INT,
quantity INT,
sale_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
FOREIGN KEY (product_id) REFERENCES products(product_id)
);
⭐ 3. Mengisi Data
3.1. Mengisi tabel customers
📝 Keterangan:
Menambahkan contoh data pelanggan.
✔ Query:
INSERT INTO customers (name, city, join_date) VALUES
('Andi', 'Jakarta', '2024-01-10'),
('Budi', 'Bandung', '2024-02-01'),
('Citra', 'Surabaya', '2024-02-15'),
('Dewi', 'Jakarta', '2024-03-01');
3.2. Mengisi tabel products
📝 Keterangan:
Menambahkan produk dengan kategori dan harga.
✔ Query:
INSERT INTO products (product_name, category, price) VALUES
('Laptop Lenovo', 'Elektronik', 8500000),
('Mouse Logitech', 'Aksesoris', 250000),
('Keyboard RGB', 'Aksesoris', 550000),
('Printer Epson', 'Elektronik', 2300000);
3.3. Mengisi tabel sales
📝 Keterangan:
Menambahkan data transaksi. Setiap baris adalah pembelian oleh pelanggan (“quantity” = jumlah beli).
✔ Query:
INSERT INTO sales (customer_id, product_id, quantity, sale_date) VALUES
(1, 1, 1, '2024-04-01'),
(1, 2, 2, '2024-04-02'),
(2, 3, 1, '2024-04-02'),
(3, 4, 1, '2024-04-05'),
(4, 1, 1, '2024-04-10'),
(4, 3, 3, '2024-04-11');
🔍 4. Query Dasar
4.1. Menampilkan semua data
📝 Keterangan:
Untuk melihat isi tabel.
✔ Query:
SELECT * FROM customers;
SELECT * FROM products;
SELECT * FROM sales;
4.2. Menampilkan transaksi lengkap dengan total harga
📝 Keterangan:
Join 3 tabel → hitung total harga (quantity × price).
✔ Query:
SELECT
s.sale_id,
c.name AS customer_name,
p.product_name,
s.quantity,
p.price,
(s.quantity * p.price) AS total_amount
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id;
📌 Hasil:
Menampilkan daftar transaksi dengan nama customer, nama produk, jumlah beli, harga satuan, dan total.
📊 5. Analisis Data
5.1. Total revenue keseluruhan
📝 Keterangan:
Menghitung total semua penjualan.
✔ Query:
SELECT
SUM(s.quantity * p.price) AS total_revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id;
5.2. Revenue per kategori
📝 Keterangan:
Hitung pendapatan per kategori produk (Elektronik, Aksesoris).
✔ Query:
SELECT
p.category,
SUM(s.quantity * p.price) AS revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.category;
5.3. Produk paling laku
📝 Keterangan:
Menghitung jumlah barang terjual per produk.
✔ Query:
SELECT
p.product_name,
SUM(s.quantity) AS total_sold
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY p.product_id
ORDER BY total_sold DESC
LIMIT 1;
5.4. Kota penyumbang revenue terbesar
📝 Keterangan:
Mengelompokkan penjualan berdasarkan kota pelanggan.
✔ Query:
SELECT
c.city,
SUM(s.quantity * p.price) AS revenue
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id
GROUP BY c.city
ORDER BY revenue DESC;
5.5. Tren penjualan harian
📝 Keterangan:
Melihat total pendapatan per tanggal.
✔ Query:
SELECT
sale_date,
SUM(quantity * price) AS daily_revenue
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY sale_date
ORDER BY sale_date;
🧼 6. Data Cleaning SQL
6.1. Mengecek duplikasi
📝 Keterangan:
Mengecek apakah ada pelanggan yang namanya sama.
✔ Query:
SELECT
name, city, COUNT(*)
FROM customers
GROUP BY name, city
HAVING COUNT(*) > 1;
6.2. Mengecek nilai NULL
✔ Query:
SELECT * FROM sales WHERE quantity IS NULL;
6.3. Update harga (misal naik 10%)
✔ Query:
UPDATE products
SET price = price * 1.1
WHERE category = 'Aksesoris';
📈 7. Analisis Lanjutan (RFM Analysis)
7.1. Recency
📝 Keterangan:
Seberapa lama sejak terakhir beli.
✔ Query:
SELECT
customer_id,
DATEDIFF(CURDATE(), MAX(sale_date)) AS recency
FROM sales
GROUP BY customer_id;
7.2. Frequency
✔ Query:
SELECT
customer_id,
COUNT(*) AS frequency
FROM sales
GROUP BY customer_id;
7.3. Monetary
✔ Query:
SELECT
s.customer_id,
SUM(s.quantity * p.price) AS monetary
FROM sales s
JOIN products p ON s.product_id = p.product_id
GROUP BY customer_id;
🏗 8. Membuat View untuk Dashboard BI
📝 Keterangan:
View memudahkan Power BI/Tableau mengambil data.
✔ Query:
CREATE VIEW vw_sales_summary AS
SELECT
c.name,
c.city,
p.product_name,
p.category,
s.quantity,
(s.quantity * p.price) AS total_amount,
s.sale_date
FROM sales s
JOIN customers c ON s.customer_id = c.customer_id
JOIN products p ON s.product_id = p.product_id;
