Напредни извештаи од базата (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.user_id  as customer_id,
    user_table.name_user  as customer_name,
    count(order_table.order_id) as number_of_orders
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
order by
    number_of_orders desc
limit 10;