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

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

{{{#!sql
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

	}}}

== Вработен кој извршил достава на најмногу нарачки
{{{#!sql
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);
}}}