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

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

select rs.id_repair_shop as rs_id , rs.rs_name as rs_name , count(r2.review_rating) as review_count ,avg(r2.review_rating)::float as review_average
from project.repair_shop rs 
left join project.repair r on r.id_repair_shop = rs.id_repair_shop 
left join project.review r2 on r2.id_repair = r.id_repair 
group by rs.id_repair_shop 

Извештај за доставувачи за нивната најдобра нарачка. Се прикажуваат информации за доставувачот, која е неговата најдобра нарачка што ја доставил и кој производ е најскап во таа нарачка и истите се сортирани според најдобрата нарачка

select nar.deliverer, nar.deliverer_username,
        max(nar.nar_suma) as najdobra_naracka, max(par.par_suma) as najskap_proizvod 
        from project.delivery_man d 
        left join 
        (
                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
                ,sum(p2.amount*ocp.quantity_order) as nar_suma
                from project.delivery_man d
                left join project.users_table ut on ut.id_user = d.id_user 
                left join project.delivery d2 on d2.id_user = d.id_user 
                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
                left join project.part p on p.id_part = ocp.id_part 
                left join project.price p2 on p2.id_part = p.id_part
                group by (d.id_user,ut.username,delivery_num)
        ) as nar on d.id_user = nar.deliverer
        left join 
        (
                select d.id_user as deliverer, ocp.id_order as order_num,d2.id_delivery as delivery_num, p.part_name as part_name,
                sum(p2.amount*ocp.quantity_order) as par_suma
                from project.delivery_man d
                left join project.users_table ut on ut.id_user = d.id_user 
                left join project.delivery d2 on d2.id_user = d.id_user 
                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
                left join project.part p on p.id_part = ocp.id_part 
                left join project.price p2 on p2.id_part = p.id_part
                group by (d.id_user,ocp.id_order,d2.id_delivery,p.part_name)
        ) as par on nar.deliverer = par.deliverer and nar.delivery_num = par.delivery_num
        group by nar.deliverer,nar.deliverer_username 
        order by najdobra_naracka desc

Извештај за успешноста на еден доставувач. Се прикажуваат информации за доставувачот, број на нарачки што ги доставил и вкупна вредност на сите нарачки што ги доставил

select nar.deliverer, nar.deliverer_username, count(distinct(nar.delivery_num)) as vkupno_dostavi,
        sum(nar.nar_suma) as vkupna_suma
        from project.delivery_man d 
        left join 
        (
                select d.id_user as deliverer, ut.username as deliverer_username, d2.id_delivery as delivery_num
                ,sum(p2.amount*ocp.quantity_order) as nar_suma
                from project.delivery_man d
                left join project.users_table ut on ut.id_user = d.id_user 
                left join project.delivery d2 on d2.id_user = d.id_user 
                left join project.order_contains_part ocp on ocp.id_order = d2.id_order 
                left join project.part p on p.id_part = ocp.id_part 
                left join project.price p2 on p2.id_part = p.id_part
                group by (d.id_user,ut.username,delivery_num)
        ) as nar on d.id_user = nar.deliverer
        group by nar.deliverer,nar.deliverer_username 
        order by vkupna_suma desc