Напредни извештаи од базата (SQL и складирани процедури)

Име на компании кои имаат завршени резервации за рути кои се случиле по последната година

-- kompanija | pochnuva_od | zavrshuva_vo | broj_zavrsheni_rezervacii

select k.kompanija_ime, 
       gp.grad_ime as pocnuva_vo,
       gz.grad_ime as zavrshuva_vo, 
       COUNT(rezervacii.rezervacija_id) as broj_zavrsheni_rezervacii
from kompanii k 
     join vozila v on k.kompanija_id = v.kompanija_id 
     join ruti r on v.vozilo_id = r.vozilo_id 
     join gradovi gp on r.zapocnuva_vo = gp.grad_id 
     join gradovi gz on r.zavrsuva_vo = gz.grad_id 
     join rezervacii rezervacii on r.ruta_id = rezervacii.ruta_id 
where lower(rezervacii.rezervacija_status) like 'zavrshena' 
     and r.datum_pristignuvanje between now()-interval '1 year' and now()
group by 1, 2, 3

Извештај за секоја компанија со број на вработени, вкупна заработувачка, вкупно клиенти, вкупно резервации за секој статус(активна, завршена, откажана, за одобрување)

select k.kompanija_ime, 
( select count(distinct v.vraboten_id)
	from vraboteni v
	where v.kompanija_id = k.kompanija_id
) as broj_vraboteni,

coalesce (( select sum(f.faktura_iznos)
	from fakturi f
	join rezervacii r on f.rezervacija_id = r.rezervacija_id 
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id 
), 0) as vkupna_zarabotuvachka,

( select count(distinct kl.klient_id)
	from klienti kl 
	join rezervacii r on kl.klient_id = r.klient_id 
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id 
) as vkupno_klienti,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id 
) as vkupno_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'aktivna'
) as vkupno_aktivni_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'zavrshena'
) as vkupno_zavrsheni_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'otkazhana'
) as vkupno_otkazhani_rezervacii,

( select count(r.rezervacija_id)
	from rezervacii r
	join ruti on r.ruta_id = ruti.ruta_id 
	join vozila on ruti.vozilo_id = vozila.vozilo_id 
	join kompanii kk on vozila.kompanija_id = kk.kompanija_id 
	where kk.kompanija_id = k.kompanija_id and lower(r.rezervacija_status) like 'za odobruvanje'
) as vkupno_za_odobruvanje_rezervacii

from kompanii k 

Кленти за секоја компанија и вкупната сума која ја имаат потрошено кај истата

select k.kompanija_ime, 
       klient.klient_ime, 
       klient.klient_prezime, 
       SUM(f.faktura_iznos) as suma_fakturi
from kompanii k
       join vozila v on k.kompanija_id = v.kompanija_id
       join ruti r on v.vozilo_id = r.vozilo_id
       join rezervacii rezervacija on r.ruta_id = rezervacija.ruta_id
       join klienti klient on rezervacija.klient_id = klient.klient_id
       join fakturi f on rezervacija.rezervacija_id = f.rezervacija_id
group by 1, klient.klient_id

Компании и вкупна сума која ја имаат за одредена релација со број на резервации на истата во последните 30 дена, подредени по опаѓачки редослед на вкупната сума

select k.kompanija_ime, 
       gA.grad_ime as pocnuva_vo,
       gB.grad_ime as zavrsuva_vo,
       SUM(f.faktura_iznos) as vkupna_suma,
       COUNT(rezervacii.rezervacija_id) as broj_rezervacii
from kompanii k
       join vozila v on k.kompanija_id = v.kompanija_id
       join ruti r on v.vozilo_id = r.vozilo_id
       join gradovi gA on r.zapocnuva_vo = gA.grad_id
       join gradovi gB on r.zavrsuva_vo = gB.grad_id
       join rezervacii rezervacii on r.ruta_id = rezervacii.ruta_id
       join fakturi f on rezervacii.rezervacija_id = f.rezervacija_id
where
       r.datum_poagjanje between now()-interval'30 day' and now()
       and
       r.datum_pristignuvanje between now()-interval'30 day' and now()
group by 1, 2, 3
order by vkupna_suma desc