Tugas Data Warehouse

-- Jumlah Semua Data
select
    (select count(*) from stores) as toko,
    (select count(*) from clothes) as baju,
    (select count(*) from employees) as karyawan,
    (select count(*) from customers) as pembeli,
    (select count(*) from suppliers) as supplier,
    (select count(*) from transaction_purchases) as transaksi_pembelian,
    (select count(*) from transaction_sales) as transaksi_penjualan,
    (select count(*) from transaction_rentals) as transaksi_penyewaan

-- Laporan Bulanan Transaksi Pembelian
select
    MONTH(transaction_date) as month,
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_purchases
group by MONTH(transaction_date), YEAR(transaction_date)
order by year, month

-- Laporan Kuartal Transaksi Pembelian
select
    QUARTER(transaction_date) as quarter,
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_purchases
group by QUARTER(transaction_date), YEAR(transaction_date)
order by year, quarter

-- Laporan Tahunan Transaksi Pembelian
select
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_purchases
group by YEAR(transaction_date)
order by year

-- Laporan Bulanan Transaksi Penjualan
select
    MONTH(transaction_date) as month,
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_sales
group by MONTH(transaction_date), YEAR(transaction_date)
order by year, month

-- Laporan Kuartal Transaksi Penjualan
select
    QUARTER(transaction_date) as quarter,
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_sales
group by QUARTER(transaction_date), YEAR(transaction_date)
order by year, quarter

-- Laporan Tahunan Transaksi Penjualan
select
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_sales
group by YEAR(transaction_date)
order by year

-- Laporan Bulanan Transaksi Penyewaan
select
    MONTH(transaction_date) as month,
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_rentals
group by MONTH(transaction_date), YEAR(transaction_date)
order by year, month

-- Laporan Kuartal Transaksi Penyewaan
select
    QUARTER(transaction_date) as quarter,
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_rentals
group by QUARTER(transaction_date), YEAR(transaction_date)
order by year, quarter

-- Laporan Tahunan Transaksi Penyewaan
select
    CONVERT(YEAR(transaction_date), CHAR) as year,
    SUM(quantity) as total_quantity,
    SUM(quantity * unit_price) as total
from transaction_rentals
group by YEAR(transaction_date)
order by year

-- Laporan Tahunan Transaksi Penjualan Berdasarkan Toko
select
    s.name as store,
    CONVERT(YEAR(ts.transaction_date), CHAR) as year,
    SUM(ts.quantity) as total_quantity,
    SUM(ts.quantity * ts.unit_price) as total
from transaction_sales ts
right join clothes c on ts.clothes_id = c.id
inner join stores s on c.store_id = s.id
group by s.id, YEAR(ts.transaction_date)
order by store, year

-- Laporan Tahunan Transaksi Penjualan Berdasarkan Toko dan Baju
select
    s.name as store,
    c.name as clothes,
    CONVERT(YEAR(ts.transaction_date), CHAR) as year,
    SUM(ts.quantity) as total_quantity,
    SUM(ts.quantity * ts.unit_price) as total
from transaction_sales ts
right join clothes c on ts.clothes_id = c.id
inner join stores s on c.store_id = s.id
group by ts.clothes_id, YEAR(ts.transaction_date)
order by store, year

-- Laporan Tahunan Transaksi Pembelian Oleh Customer
select
    c.name as customer,
    CONVERT(YEAR(ts.transaction_date), CHAR) as year,
    SUM(ts.quantity) as total_quantity,
    SUM(ts.quantity * ts.unit_price) as total
from transaction_sales ts
right join customers c on c.id  = ts.customer_id
group by ts.customer_id , YEAR(ts.transaction_date)
order by customer, year

-- Laporan Total Transaksi Penjualan Tertinggi Oleh Toko
select
    s.name as store,
    SUM(ts.quantity) as total_quantity,
    SUM(ts.quantity * ts.unit_price) as total
from transaction_sales ts
right join clothes c on c.id  = ts.clothes_id
inner join stores s on s.id = c.store_id
group by c.store_id
order by total desc

-- Laporan Total Transaksi Pembelian Tertinggi Oleh Customer
select
    c.name as customer,
    SUM(ts.quantity) as total_quantity,
    SUM(ts.quantity * ts.unit_price) as total
from transaction_sales ts
right join customers c on c.id  = ts.customer_id
group by ts.customer_id
order by total desc

-- Laporan Transaksi Pembelian Berdasarkan Jenis Kelamin Customer
select
    c.gender,
    SUM(ts.quantity) as total_quantity,
    SUM(ts.quantity * ts.unit_price) as total
from transaction_sales ts
right join customers c on c.id  = ts.customer_id
group by c.gender
order by total desc