SELECT k.nama, d.nama AS departemen, g.gaji_pokok,
       RANK() OVER (PARTITION BY k.id_departemen
                      ORDER BY g.gaji_pokok DESC) AS rank_gaji
FROM karyawan k
JOIN departemen d ON k.id_departemen = d.id
JOIN gaji g ON g.id_karyawan = k.id
WHERE g.tahun = 2023
ORDER BY departemen, rank_gaji

WITH dept_stats AS (
  SELECT d.nama AS departemen,
         COUNT(*) AS total_karyawan,
         SUM(CASE WHEN k.status = 'resign' THEN 1 ELSE 0 END) AS jumlah_resign
  FROM karyawan k
  JOIN departemen d ON k.id_departemen = d.id
  GROUP BY d.id
)
SELECT departemen, total_karyawan, jumlah_resign,
       ROUND(jumlah_resign * 100.0 / total_karyawan, 1) AS resign_rate_pct
FROM dept_stats
ORDER BY resign_rate_pct DESC

WITH avg_gaji_dept AS (
  SELECT k.id_departemen,
         AVG(g.gaji_pokok) AS avg_gaji
  FROM gaji g JOIN karyawan k ON g.id_karyawan = k.id
  WHERE g.tahun = 2023
  GROUP BY k.id_departemen
)
SELECT k.nama, d.nama AS departemen,
       g.gaji_pokok, kn.skor, kn.predikat
FROM karyawan k
JOIN departemen d    ON k.id_departemen  = d.id
JOIN gaji g          ON g.id_karyawan   = k.id AND g.tahun = 2023
JOIN kinerja kn      ON kn.id_karyawan  = k.id AND kn.tahun = 2023
JOIN avg_gaji_dept agd ON agd.id_departemen = k.id_departemen
WHERE kn.predikat = 'Excellent'
  AND g.gaji_pokok < agd.avg_gaji
ORDER BY g.gaji_pokok

SELECT p.nama AS pelanggan, p.kota,
       MAX(ps.tanggal) AS last_order,
       JULIANDAY('2024-04-01') - JULIANDAY(MAX(ps.tanggal)) AS hari_sejak
FROM pesanan ps
JOIN pelanggan p ON ps.id_pelanggan = p.id
GROUP BY p.id
ORDER BY hari_sejak ASC

WITH produk_rev AS (
  SELECT pr.nama, pr.kategori,
         SUM(dp.qty * dp.harga_satuan) AS revenue
  FROM detail_pesanan dp
  JOIN produk pr ON dp.id_produk = pr.id
  GROUP BY pr.id
)
SELECT nama, kategori, revenue,
       SUM(revenue) OVER (ORDER BY revenue DESC
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS kumulatif
FROM produk_rev
ORDER BY revenue DESC

SELECT mk.nama AS mata_kuliah,
       n.grade,
       COUNT(*) AS jumlah,
       ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER
         (PARTITION BY mk.kode), 1) AS pct
FROM nilai n
JOIN mata_kuliah mk ON n.kode_mk = mk.kode
GROUP BY mk.kode, n.grade
ORDER BY mk.nama, n.grade

SELECT m.nama, m.program_studi, m.ipk,
       ROUND(avg_prodi.avg_ipk, 2) AS rata_prodi
FROM mahasiswa m
JOIN (
  SELECT program_studi, AVG(ipk) AS avg_ipk
  FROM mahasiswa
  GROUP BY program_studi
) avg_prodi ON m.program_studi = avg_prodi.program_studi
WHERE m.ipk > avg_prodi.avg_ipk
ORDER BY m.program_studi, m.ipk DESC

SELECT b.judul, b.pengarang, b.kategori,
       COUNT(p.id) AS total_pinjam
FROM buku b
LEFT JOIN peminjaman p ON b.id = p.id_buku
GROUP BY b.id
ORDER BY total_pinjam DESC
LIMIT 5

SELECT a.nama AS anggota, b.judul,
       p.tanggal_pinjam,
       COALESCE(p.tanggal_kembali, 'Belum dikembalikan') AS kembali,
       CASE
         WHEN p.tanggal_kembali IS NULL
           THEN CAST(JULIANDAY('now') - JULIANDAY(p.tanggal_pinjam) AS INTEGER)
         ELSE 0
       END AS hari_terlambat
FROM peminjaman p
JOIN anggota a ON p.id_anggota = a.id
JOIN buku b    ON p.id_buku    = b.id
ORDER BY hari_terlambat DESC