Напредни извештаи од базата (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
Last modified
7 months ago
Last modified on 04/10/24 19:50:18
Note:
See TracWiki
for help on using the wiki.