-- students table
create table murid (
   id int auto_increment primary key,
   nisn varchar(20) not null,
   nama varchar(100) not null
);

-- kesalahan table
create table kesalahan (
   id int auto_increment primary key,
   kode varchar(20) not null,
   nama varchar(100) not null,
   bobot decimal(10,2) default 0
);

-- intermediate/junction table
create table kesalahan_murid (
   id int auto_increment primary key,
   id_murid int not null,
   id_kesalahan int not null,
   tanggal date not null,
   constraint fk_kesalahan_murid_murid 
   foreign key (id_murid) references murid (id),
   constraint fk_kesalahan_murid_kesalahan 
   foreign key (id_kesalahan) references kesalahan (id)
);

-- students data
insert into murid (nisn, nama)
values ('001','Nur Hidayat')
, ('002','Arieditya Pr. Dh.')
, ('003','Peter Jack Kambey');

-- kesalahan data
insert into kesalahan (kode, nama,bobot)
values ('K001','Mencontek saat Ujian',0.50)
, ('K002','Membolos Sekolah',0.25)
, ('K003','Merokok dalam Kelas',0.25);

-- data kesalahan 
insert into kesalahan_murid (id_murid,id_kesalahan,tanggal)
values (1,1,'2018-04-01')
, (1,2,'2018-04-02'), (1,3,'2018-04-03')
, (1,3,'2018-04-13'), (2,1,'2018-04-01')
, (2,2,'2018-04-02'), (2,1,'2018-04-03')
, (2,2,'2018-04-04'), (2,3,'2018-04-15')
, (2,2,'2018-04-14'), (2,2,'2018-04-14')
, (2,3,'2018-04-25'), (3,1,'2018-04-11')
, (3,1,'2018-04-12'), (3,1,'2018-04-24');

-- query 1
 select a.nisn AS nisn_murid
, a.nama AS nama_murid
, c.kode AS kode_kesalahan
, c.nama AS nama_kesalahan
, c.bobot AS bobot_kesalahan
, count(km.id) AS jumlah_kesalahan 
from dss.murid a 
join dss.kesalahan c on 1 = 1 
left join dss.kesalahan_murid km on a.id = km.id_murid and c.id = km.id_kesalahan
group by a.nama, c.nama 
order by a.nama,c.nama

-- query 2
select *
    , max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan )  AS pembagi_normalisasi
    , (jumlah_kesalahan / max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan ) ) AS nilai_normalisasi
    , ((jumlah_kesalahan / max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan ) ) * bobot_kesalahan) AS nilai_pembobotan 
from (
      select a.nisn AS nisn_murid, a.nama AS nama_murid
      , c.kode AS kode_kesalahan, c.nama AS nama_kesalahan
      , c.bobot AS bobot_kesalahan, count(km.id) AS jumlah_kesalahan 
      from dss.murid a join dss.kesalahan c on 1 = 1 
      left join dss.kesalahan_murid km on a.id = km.id_murid and c.id = km.id_kesalahan
      group by a.nama, c.nama 
      order by a.nama,c.nama
) matriks

-- query 3
select *  , sum(pembobotan.nilai_pembobotan) OVER (PARTITION BY pembobotan.nisn_murid) AS nilai_akhir 
from (
    select *
    , max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan )  AS pembagi_normalisasi
    , (jumlah_kesalahan / max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan ) ) AS nilai_normalisasi
    , ((jumlah_kesalahan / max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan ) ) * bobot_kesalahan) AS nilai_pembobotan 
    from (
      select a.nisn AS nisn_murid, a.nama AS nama_murid, c.kode AS kode_kesalahan
      , c.nama AS nama_kesalahan, c.bobot AS bobot_kesalahan, count(km.id) AS jumlah_kesalahan 
      from dss.murid a join dss.kesalahan c on 1 = 1 
      left join dss.kesalahan_murid km on a.id = km.id_murid and c.id = km.id_kesalahan
      group by a.nama, c.nama 
      order by a.nama,c.nama
    ) matriks
) pembobotan

-- query 4
select nisn_murid AS nisn_murid, nama_murid AS nama_murid
, sum((case when (kode_kesalahan = 'K001') then nilai_pembobotan else 0 end)) AS mencontek
, sum((case when (kode_kesalahan = 'K002') then nilai_pembobotan else 0 end)) AS membolos
, sum((case when (kode_kesalahan = 'K003') then nilai_pembobotan else 0 end)) AS merokok
, max(nilai_akhir) AS nilai_akhir 
from (
    select *  , sum(pembobotan.nilai_pembobotan) OVER (PARTITION BY pembobotan.nisn_murid) AS nilai_akhir 
    from (
        select *
        , max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan )  AS pembagi_normalisasi
        , (jumlah_kesalahan / max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan ) ) AS nilai_normalisasi
        , ((jumlah_kesalahan / max(jumlah_kesalahan) OVER (PARTITION BY kode_kesalahan ) ) * bobot_kesalahan) AS nilai_pembobotan 
        from (
            select a.nisn AS nisn_murid, a.nama AS nama_murid, c.kode AS kode_kesalahan
            , c.nama AS nama_kesalahan, c.bobot AS bobot_kesalahan, count(km.id) AS jumlah_kesalahan 
            from dss.murid a join dss.kesalahan c on 1 = 1 
            left join dss.kesalahan_murid km on a.id = km.id_murid and c.id = km.id_kesalahan
            group by a.nama, c.nama 
            order by a.nama,c.nama
        ) matriks
    ) pembobotan
) akhirnya 
group by nisn_murid, nama_murid 
order by nilai_akhir desc