Напредни извештаи од базата (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,
    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, average_rating;