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

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

select os.names, os.store_id,
coalesce((
select count(pis.product_in_store_id)
        from online_stores as os
        join product_in_store as pis on os.store_id=pis.store_id
        ),0) as vkupno_proizvodi,
(
select sum(p.price)
        from online_stores as os
        join product_in_store as pis on os.store_id=pis.store_id
        join price as p on pis.product_in_store_id=p.product_in_store_id
        
) as vkupna_vrednost,
(
select avg(p.price)


        from online_stores as os
        join product_in_store as pis on os.store_id=pis.store_id
        join price as p on pis.product_in_store_id=p.product_in_store_id
        
) as prosek_vrednost,
(
select count(sb.shopping_id)
        from online_stores as os 
        join product_in_store as pis on os.store_id=pis.product_id
        join orders as ord on pis.product_in_store_id=ord.product_in_store_id
        join shopping_bag as sb on ord.shopping_id=sb.shopping_id
        where sb.order_date between now() - interval '3 months' and now()
) as vkupno_naracki

        
from online_stores as os
group by 1, 2

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

create view vkupen_broj_naracki as 

select se.shipping_employee_id, u.user_name, u.surname, count(s.shipping_id) as broj_naracki
from shipping_employee as se
join users as u on se.user_id=u.user_id
join shipping as s on se.user_id=s.user_id
join shopping_bag as sb on s.shopping_id=sb.shopping_id

group by 1, 2, 3;

select vbn.shipping_employee_id, vbn.user_name, vbn.surname, vbn.broj_naracki as najmnogu_naracki
from vkupen_broj_naracki as vbn
where vbn.broj_naracki = (select max(broj_naracki) from vkupen_broj_naracki);

Вработените од сите продавници со искуство од 6 или повеќе години и нивната улога во продавницата

create view broj_vraboteni as

select os.store_id, os.names, u.user_name, u.surname, wa.works_from, r.role_name, r.role_desc
from online_stores as os
join store_employee as se on os.store_id=se.store_id
join users as u on se.user_id=u.user_id
join works_as as wa on se.user_id=wa.user_id
join roles as r on wa.id_role=r.id_role
where extract(year from now()) - extract(year from wa.works_from) >= 6