I Gusti Putu Yoga Adhi Karisma, M.Sc.

import pandas as pd

df = pd.read_csv("penjualan_mentah.csv")

# 1. Drop duplicates by order_id
df = df.drop_duplicates(subset="order_id")

# 2. Standardize date formats
df["tgl_order"] = pd.to_datetime(df["tgl_order"], dayfirst=True, errors="coerce")

# 3. Price: strip "Rp" and dots, convert to a number
df["harga"] = (df["harga"].astype(str)
               .str.replace(r"[^0-9]", "", regex=True)
               .replace("", None).astype(float))

# 4. Standardize text
df["kota"] = df["kota"].str.strip().str.title().replace({"Jkt": "Jakarta"})
df["kategori"] = df["kategori"].str.strip().str.title()
df["status"] = df["status"].str.strip().str.capitalize()

# 5. Fill missing prices with the median per category
df["harga"] = df["harga"].fillna(df.groupby("kategori")["harga"].transform("median"))

# 6. Drop cancelled orders and invalid quantities
df = df[(df["status"] == "Selesai") & (df["qty"] > 0)]

-- Revenue per month
SELECT DATE_FORMAT(tgl_order, '%Y-%m') AS bulan,
       COUNT(*)         AS jumlah_order,
       SUM(qty * harga) AS pendapatan
FROM penjualan_bersih
GROUP BY bulan
ORDER BY bulan;

-- Contribution by category
SELECT kategori,
       SUM(qty * harga) AS pendapatan,
       ROUND(100 * SUM(qty * harga) /
         (SELECT SUM(qty * harga) FROM penjualan_bersih), 1) AS persen
FROM penjualan_bersih
GROUP BY kategori
ORDER BY pendapatan DESC;