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

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

select
    count(order_table.order_id),
    product.product_name
from 
    order_table
left join order_table_contains_product on order_table_contains_product.order_id = order_table.order_id
left join product on product.product_id  = order_table_contains_product.product_id
where
    order_table.order_status = 'Delivered' 
    and order_table.order_date between  now() - interval '3 months' and now()
group by 
    product.product_name

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

select
    category.category_name as Category,
    product.product_name as Product_Name,
    count(order_table_contains_product.product_id) as Number_of_orders
from
    category
join product_is_in_category on category.category_id = product_is_in_category.category_id
join product on product_is_in_category.product_id = product.product_id
join order_table_contains_product on product.product_id = order_table_contains_product.product_id
group by
    category.category_id,
    category.category_name,
    product.product_name
having
    count(order_table_contains_product.product_id) = (
        select
            count(*)
        from
            order_table_contains_product
        join product on order_table_contains_product.product_id = product.product_id
        join product_is_in_category on product.product_id = product_is_in_category.product_id
        where
            product_is_in_category.category_id = category.category_id
       group by
            order_table_contains_product.product_id
    )

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

select
    category.category_name as category,
    product.product_name as product_name,
    count(review.review_rating) as number_of_reviews,
    avg(review.review_rating) as average_rating
from
    category
join product_is_in_category on category.category_id = product_is_in_category.category_id
join product on product_is_in_category.product_id = product.product_id
join order_table_contains_product on product.product_id = order_table_contains_product.product_id
join order_table on order_table_contains_product.order_id = order_table.order_id
join review on product.product_id = review.product_id
where
    order_table.order_date between now() - interval '6 months' and now()
group by
    category.category_name,
    product.product_name
order by
    category, product_name,number_of_reviews, average_rating;

Извештај за 10те најактивни корисници со најголем број нарачки

select
    customer_id,
    customer_name,
    number_of_orders
from
    (
        select
            customer.user_id  as customer_id,
            user_table.name_user  as customer_name,
            count(order_table.order_id) as number_of_orders,
            row_number() over (order by count(order_table.order_id) desc) as row_num
        from
            customer
        join order_table on customer.user_id = order_table.customer_id
        join user_table on customer.user_id = user_table.user_id
        group by
            customer.user_id,
            user_table.name_user
    ) as ranked_orders
where
    row_num <= 10;

Извештај за остатокот од гаранцијата во денови на купениот производ

select
    category.category_name,
    product.product_name,
    order_table.order_id,
    date_trunc('day', (order_table.order_date + interval '1 year' * product.product_warranty) - now()) as remaining_warranty
from
    order_table
join order_table_contains_product on order_table.order_id = order_table_contains_product.order_id
join product on order_table_contains_product.product_id = product.product_id
join product_is_in_category on product_is_in_category.product_id = product.product_id
join category on category.category_id = product_is_in_category.product_id

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

select 
    user_table.name_user,
    count(delivery.delivery_id),
    sum(product.product_price)
from 
    user_table
join delivery_man on delivery_man.user_id = user_table.user_id
join delivery on delivery.delivery_man_id = delivery_man.user_id
join order_table on order_table.order_id = delivery.order_id 
join order_table_contains_product on order_table_contains_product.order_id = order_table.order_id 
join product on product.product_id = order_table_contains_product.product_id
where
    order_table.order_status = 'Delivered'
group by 
    user_table.name_user