Tanya data usaha Anda, dijawab di WhatsApp

SELECT c.nama, SUM(t.total) AS omzet
FROM transaksi t
JOIN cabang c ON c.id = t.cabang_id
WHERE t.tanggal BETWEEN '2026-08-01' AND '2026-08-07'
GROUP BY c.nama
ORDER BY omzet DESC;

WITH mingguan AS (
  SELECT cabang_id, date_trunc('week', tanggal) AS minggu,
         SUM(total) AS omzet
  FROM transaksi
  GROUP BY 1, 2
)
SELECT c.nama,
       round(100.0 * (m.omzet - lag(m.omzet) OVER w)
             / lag(m.omzet) OVER w, 1) AS perubahan_persen
FROM mingguan m
JOIN cabang c ON c.id = m.cabang_id
WINDOW w AS (PARTITION BY m.cabang_id ORDER BY m.minggu)
ORDER BY perubahan_persen;

SELECT p.nama, SUM(ti.qty) AS porsi
FROM transaksi_item ti
JOIN produk p ON p.id = ti.produk_id
JOIN transaksi t ON t.id = ti.transaksi_id
WHERE date_trunc('month', t.tanggal) = date_trunc('month', current_date)
GROUP BY p.nama
ORDER BY porsi DESC
LIMIT 5;

SELECT to_char(t.waktu, 'HH24:00') AS jam,
       round(count(*)::numeric / count(DISTINCT t.tanggal)) AS rata_transaksi
FROM transaksi t
JOIN cabang c ON c.id = t.cabang_id
WHERE c.nama = 'Tembalang'
  AND t.tanggal >= current_date - interval '30 days'
GROUP BY jam
ORDER BY jam;

SELECT b.nama,
       floor(s.jumlah / NULLIF(p.rata_pakai_harian, 0)) AS sisa_hari
FROM stok s
JOIN bahan b ON b.id = s.bahan_id
JOIN pemakaian_harian p ON p.bahan_id = s.bahan_id
WHERE s.jumlah < b.stok_aman
ORDER BY sisa_hari;