Вкупен приход по продавница во изминатите 3 месеци

select sum(t1.Anhoch+t2.Anhoch) as Anhoch, sum(t1.Setec + t2.Setec) as Setec, sum(t1.Neptun+t2.Neptun) as Neptun
from
(
select
sum(case when pr.store_name = 'Anhoch' then cp.quantity * pr.price else 0 end) as Anhoch,
sum(case when pr.store_name = 'Setec' then cp.quantity * pr.price else 0 end) as Setec,
sum(case when pr.store_name = 'Neptun' then cp.quantity * pr.price else 0 end) as Neptun
from project.order as o
join project.checkout as ch on o.order_id = ch.order_id
join project.cart as cr on cr.cart_id = ch.cart_id
join project.contains_product as cp on cp.cart_id = cr.cart_id
join project.product as pr on pr.prod_id = cp.prod_id
join project.store as str on pr.store_name = str.store_name
join project.delivery as dlv on dlv.delivery_id = o.delivery_id
where now() - interval '3 months' <= dlv."date" and dlv.status='Delivered'
) as t1,
(
select
sum(case when pr.store_name = 'Anhoch' then ccp.num_products_config * pr.price else 0 end) as Anhoch,
sum(case when pr.store_name = 'Setec' then ccp.num_products_config * pr.price else 0 end) as Setec,
sum(case when pr.store_name = 'Neptun' then ccp.num_products_config * pr.price else 0 end) as Neptun
from project.order as o
join project.orders_configuration as oc on o.order_id = oc.order_id
join project."Configuration" as cfg on oc.config_id = cfg.config_id
join project.config_contains_product as ccp on cfg.config_id = ccp.config_id
join project.product as pr on pr.prod_id = ccp.prod_id
join project.store as str on pr.store_name = str.store_name
join project.delivery as dlv on dlv.delivery_id = o.delivery_id
where now() - interval '3 months' <= dlv."date" and dlv.status='Delivered'
) as t2

5 Најпопуларни производи во изминатите 6 месеци

select distinct p.name, count(cp.prod_id) as cnt
from project.product as p
join project.contains_product as cp on cp.prod_id = p.prod_id
join project.cart as crt on crt.cart_id = cp.cart_id
join project.checkout as chk on chk.cart_id = crt.cart_id
join project.order as o on o.order_id = chk.order_id
join project.delivery as dlv on o.delivery_id = dlv.delivery_id
where dlv.status = 'Delivered' and now()-interval'6 months' <= dlv."date"
group by p.prod_id,cp.prod_id 
order by cnt desc
limit 5;

5 Најпопуларни конфигурации во изминатите 6 месеци

select distinct c."name", count(oc.config_id) as cnt
from project."Configuration" as c
join project.orders_configuration as oc on oc.config_id = c.config_id
join project."order" as o on o.order_id = oc.order_id 
join project.delivery as dlv on dlv.delivery_id = o.delivery_id
where now() - interval'6 months' <= dlv."date" and dlv.status = 'Delivered'
group by c.name, oc.config_id
order by cnt desc
limit 5;

Просечна цена на продуктот во месец од изминатите 12 месеци

select 
sum(case when extract(month from ph.dates) =1 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =1 and now() - interval'12 months' <= ph.dates then ph.price end) as Januari, 
sum(case when extract(month from ph.dates) =2 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =2 and now() - interval'12 months' <= ph.dates then ph.price end) as Fevruari,
sum(case when extract(month from ph.dates) =3 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =3 and now() - interval'12 months' <= ph.dates then ph.price end) as Mart,
sum(case when extract(month from ph.dates) =4 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =4 and now() - interval'12 months' <= ph.dates then ph.price end) as April,

sum(case when extract(month from ph.dates) =5 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =5 and now() - interval'12 months' <= ph.dates then ph.price end) as Maj,
sum(case when extract(month from ph.dates) =6 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =6 and now() - interval'12 months' <= ph.dates then ph.price end) as Juni,
sum(case when extract(month from ph.dates) =7 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =7 and now() - interval'12 months' <= ph.dates then ph.price end) as Juli,
sum(case when extract(month from ph.dates) =8 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =8 and now() - interval'12 months' <= ph.dates then ph.price end) as Avgust,

sum(case when extract(month from ph.dates) =9 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =9 and now() - interval'12 months' <= ph.dates then ph.price end) as Septemvri,
sum(case when extract(month from ph.dates) =10 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =10 and now() - interval'12 months' <= ph.dates then ph.price end) as Oktomvri,
sum(case when extract(month from ph.dates) =11 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =11 and now() - interval'12 months' <= ph.dates then ph.price end) as Noemvri,
sum(case when extract(month from ph.dates) =12 and now() - interval'12 months' <= ph.dates then ph.price end)/count(case when extract(month from ph.dates) =12 and now() - interval'12 months' <= ph.dates then ph.price end) as Dekemvri

from project.price_history as ph
where prod_id = 3250;

Најпопуларен производ во wishlists на корисниците во изминатите 6 месеци

select p."name" as Product, p.description as Description
from project.product as p
join(
        select ptw.prod_id, count(ptw.prod_id) as cnt
        from project.product_to_wishlist as ptw
        group by ptw.prod_id
) as pw on p.prod_id = pw.prod_id
join(
                select u.time_stamp, u.product_id
                from project.updates as u
                group by u.product_id, u.time_stamp
)as u on u.product_id = p.prod_id
where now()-interval'6 months' <= u.time_stamp
group by p."name", p.description, pw.cnt, p.prod_id, u.time_stamp
order by pw.cnt desc
limit 1;

Доставувач со највеќе доставени продукти во изминатите 3 месеци

select o.distributor_id as Dostavuvac , sum(d.cnt) as Br_Dostaveni_Produkti
from project.distributor as o
join (
                select count(d.delivery_id) as cnt ,d.distributor_id, d.delivery_id
                from project.delivery as d
                where status='Delivered' AND now() - interval'3 months' <= d."date"
                group by d.delivery_id, d.distributor_id
)as d on o.distributor_id = d.distributor_id
group by o.distributor_id
order by Br_Dostaveni_Produkti desc
limit 1;

Вкупно продадени продукти по категорија

        select distinct pr.type, sum(cp.quantity)
        from project.contains_product as cp
        join project.product as pr on pr.prod_id = cp.prod_id
        join project.cart as crt on crt.cart_id = cp.cart_id
        join project.checkout as chk on chk.cart_id = crt.cart_id
        join project.order as o on o.order_id = chk.order_id
        where o.order_status = 'Processed'
        group by pr."type"