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

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

SELECT s.supplierId, s.supplier_name, q1.products_count, q2.total_quantity_in_warehouses, COALESCE(COUNT(DISTINCT pw.warehouseId), 0) AS distinct_warehouses_count, COALESCE(SUM(oi.quantity), 0) AS total_quantity_in_order_items
FROM suppliers s
LEFT JOIN ( SELECT s.supplierId, COUNT(DISTINCT p.productId) AS products_count
FROM suppliers s
LEFT JOIN products p ON s.supplierId = p.supplierId
GROUP BY s.supplierId )
q1 ON s.supplierId = q1.supplierId

LEFT JOIN (
SELECT s.supplierId, COALESCE(SUM(pw.quantity), 0) AS total_quantity_in_warehouses
FROM suppliers s
LEFT JOIN products p ON s.supplierId = p.supplierId
LEFT JOIN products_in_warehouse pw ON p.productId = pw.productId
GROUP BY s.supplierId )
q2 ON s.supplierId = q2.supplierId

LEFT JOIN products p ON s.supplierId = p.supplierId
LEFT JOIN products_in_warehouse pw ON p.productId = pw.productId
LEFT JOIN order_item oi ON p.productId = oi.productId
GROUP BY s.supplierId, s.supplier_name, q1.products_count, q2.total_quantity_in_warehouses
HAVING q1.products_count > 0;

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

WITH Sales_Table AS 
  (SELECT *, RANK () OVER (ORDER BY Total_Quantity DESC) Sales_Rank 
  FROM 
    (SELECT p.product_name, SUM (quantity) AS Total_Quantity 
    FROM order_item oi 
    JOIN products p on p.productid = oi.productid
    GROUP BY oi.productid, p.product_name
    ORDER BY Total_Quantity)
  )

SELECT * 
FROM Sales_Table 
WHERE Sales_Rank <= 5 
UNION
SELECT * 
FROM Sales_Table 
WHERE Sales_Rank >= ((SELECT MAX(Sales_Rank) FROM Sales_Table)-5)
ORDER BY Total_Quantity DESC;

3. Приказ на држави кои имаат најголем број на нарачки и во проценти изразено колку од вкупните нарачки доаѓаат од таа држава

SELECT c.country , COUNT(o.orderid) AS order_count, ROUND(COUNT(c.country)*100/(SELECT COUNT(*) FROM orders),2) AS percentage 
FROM orders o 
JOIN customers c On o.customerid = c.userid  
GROUP BY c.country 
ORDER BY order_count DESC 
LIMIT 20;

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

WITH status AS 
  (SELECT w.warehouseid, w.warehouse_name, p.product_name, SUM(piw.quantity) AS in_stock , SUM(distinct piw.max_stock) as max_stock
  FROM warehouses w 
  NATURAL JOIN products_in_warehouse piw  
  NATURAL JOIN products p
  WHERE p.productid IN 
    (SELECT q1.productid 
    FROM
      (SELECT p.productid, p.product_name, SUM(oi.quantity) AS total_quantity
      FROM order_item oi 
      NATURAL JOIN products p 
      GROUP BY p.productid
      ORDER BY total_quantity DESC
      LIMIT 5) q1) 
  GROUP BY w.warehouseid, p.productid)

SELECT warehouseid,warehouse_name,product_name,in_stock, 
CASE 
	WHEN in_stock < (max_stock/2) THEN 'Order Immerdiately'
	WHEN in_stock >= (max_stock/2) AND in_stock < (max_stock*0.8) THEN 'Order Soon'
ELSE 'Safe Level'
END AS stock_status 
FROM status 
ORDER BY in_stock ASC 
LIMIT 20;