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

За секое продажно место, да се најдат пакетите кои се најчесто продавани, од кои продукти се состои пакетот, вкупна количина на продадени пакети, како и името на вработениот кој го додал пакетот

create view najdobri_paketi_za_sekoe_prodazno_mesto as select pr.ime, pa.paket_id, string_agg(distinct h.ime_hra, ', ') as se_sostoi_od, sum(nip.kolicina) vkupno_prodadeni,
(
        select k.ime 
        from korisnik k join vrabotenpd v on k.korisnik_id = v.korisnik_id 
        join paket paa on paa.vraboten_id = v.korisnik_id 
        where paa.paket_id = pa.paket_id 
) as ime_vraboten
from prodazhnomesto pr join prodazhnomesto_nudi_paket pnp on pr.id_mesto = pnp.id_mesto 
join paket pa on pa.paket_id = pnp.paket_id 
join paket_sodrzi_hrana psh  on psh.paket_id = pa.paket_id 
join hrana h on h.id_stavka = psh.id_stavka
join naracka_ima_paket nip on nip.paket_id = pa.paket_id 
group by pr.id_mesto, pa.paket_id
having sum(nip.kolicina)
= (
select sum(nipp.kolicina) 
from prodazhnomesto prr join prodazhnomesto_nudi_paket pnpp on prr.id_mesto = pnpp.id_mesto 
join paket paa on paa.paket_id = pnpp.paket_id 
join paket_sodrzi_hrana pshh  on pshh.paket_id = paa.paket_id 
join hrana hh on hh.id_stavka = pshh.id_stavka
join naracka_ima_paket nipp on nipp.paket_id = paa.paket_id 
group by prr.id_mesto, paa.paket_id
order by sum(nipp.kolicina) 
limit 1
);

За секое продажно место да се најде потрошувачот кој направил најмногу нарачки, адресата на соодветниот потрошувач и колку нарачки направил во продажното место

create view najveren_potrosuvac_za_sekoe_prodazno_mesto as select k.ime as  ime_potrosuvac, pr.ime as ime_mesto, l.adresa as adresa_potrosuvac, l.broj as broj_adresa_potrosuvac,  count(distinct n.naracka_id) as broj_naracka  
from korisnik k join potrosuvac p on k.korisnik_id = p.korisnik_id 
join naracka n on n.potrosuvac_id = p.korisnik_id 
right join prodazhnomesto pr on pr.id_mesto = n.id_mesto 
left join lokacija l on l.lokacija_id = p.lokacija_id 
group by k.korisnik_id, pr.id_mesto, l.adresa, l.broj  
having count(distinct n.naracka_id)
= 
                (select  count(distinct nn.naracka_id)  
                from korisnik kk join potrosuvac pp on kk.korisnik_id = pp.korisnik_id 
                join naracka nn on nn.potrosuvac_id = pp.korisnik_id 
                right join prodazhnomesto prr on prr.id_mesto = nn.id_mesto 
                left join lokacija ll on ll.lokacija_id = pp.lokacija_id 
                where pr.id_mesto = prr.id_mesto 
                group by kk.korisnik_id, prr.id_mesto, ll.adresa, ll.broj  
                order by count(distinct nn.naracka_id) desc
                limit 1
                
                );

Да се најде доставувачот кој што има доставено најмногу нарачки, број на нарачки кои ги има доставено, вкупен износ од нарачките, просечен износ од нарачките како и името на администраторот кој го додал

create view najdobar_dostavuvac as select k.ime, (
        select count(distinct naplata_id) from naplata nn
        where nn.dostavuvac_id = k.korisnik_id 
) as broj_naplati,
(
        select sum(iznos) from naplata nn
        where nn.dostavuvac_id = k.korisnik_id 
) as vkupna_suma,
(
        select avg(iznos) from naplata nn
        where nn.dostavuvac_id = k.korisnik_id 
) as prosecna_suma,
(
        select kk.ime
        from korisnik kk join admin_table at2 on kk.korisnik_id = at2.korisnik_id 
        join dostavuvac d2 on d2.admin_id = at2.korisnik_id 
        where d2.korisnik_id = k.korisnik_id 
) as admin_koj_go_dodal
from korisnik k join dostavuvac d on k.korisnik_id  = d.korisnik_id 
join naracka n on n.dostavuvac_id = d.korisnik_id 
join naplata nap on nap.naplata_id  = n.naplata_id
group by k.korisnik_id 
having count(distinct n.naplata_id) = 
(
        select count(naplata_id)
        from naplata n 
        group by dostavuvac_id
        order by count(naplata_id) desc
        limit 1
);

Да се прикажат потрошувачите заедно со вкупниот износ на купони кои го имат добиено

create view potrosuvaci_vkupen_iznos_kuponi as select ime, sum(ku.iznos_kupon) as vkupen_iznos
 from korisnik k join potrosuvac p on p.korisnik_id = k.korisnik_id 
 join kupon ku on ku.potrosuvac_id = p.korisnik_id 
group by k.korisnik_id; 

Да се најде продажното место кое остварило најголем промет во последните три месеци

create view najprometno_prodazno_mesto as select pr.ime, sum(iznos) as promet
from prodazhnomesto pr  join naracka n on pr.id_mesto = n.id_mesto 
join naplata nap on nap.naplata_id = n.naplata_id 
and nap.datum_naplata between now () - interval '3 months' and now()
group by pr.id_mesto
having sum(iznos)
= (
        select sum(iznos) 
        from prodazhnomesto pr  join naracka n on pr.id_mesto = n.id_mesto 
         join naplata nap on nap.naplata_id = n.naplata_id 
        and nap.datum_naplata between now () - interval '3 months' and now()
        group by pr.id_mesto
        order by sum(iznos) desc
        limit 1
);

Да се најдат храните кои се продаваат најмногу за дадени три календарски месеци во годината

create function najprodavano_za_dadeni_meseci(month_1 int, month_2 int, month_3 int)
returns table(
        id_stavka int,
        ime_hra varchar(200),
        id_mesto int,
        ime_stavka varchar(200),
        vkupno_prodadeni bigint
)
language plpgsql
as $$
begin   
        return query select h.id_stavka, h.ime_hra,pr.id_mesto, pr.ime as ime_stavka, sum(nsh.kolicina) as vkupno_prodadeni
        from naracka n join naracka_sodrzi_hrana nsh on n.naracka_id = nsh.naracka_id 
        join hrana h on nsh.id_stavka = h.id_stavka 
        join prodazhnomesto pr on pr.id_mesto = n.id_mesto 
        where (lower(n.status) like '%zavrsena%' or lower(n.status) like '%завршена%')
        and (extract (month from n.datum_naracka) in (month_1,month_2,month_3) and extract(year from now()) = extract (year from n.datum_naracka) )
        group by h.id_stavka, pr.id_mesto,h.ime_hra, pr.ime 
        having sum(nsh.kolicina) = 
 (
                select  sum(nshh.kolicina)
                from naracka nn join naracka_sodrzi_hrana nshh on nn.naracka_id = nshh.naracka_id 
                join hrana hh on nshh.id_stavka = hh.id_stavka 
                join prodazhnomesto prr on prr.id_mesto = nn.id_mesto 
                where (lower(nn.status) like '%zavrsena%' or lower(nn.status) like '%завршена%')
                and (extract (month from nn.datum_naracka) in (month_1,month_2,month_3) and extract(year from now()) = extract (year from nn.datum_naracka) )
                group by hh.id_stavka, prr.id_mesto,hh.ime_hra, prr.ime 
                having prr.id_mesto = pr.id_mesto
                order by sum(nshh.kolicina) desc 
                limit 1
);
end
$$