Changes between Version 6 and Version 7 of AdvancedReports

Timestamp:

05/28/26 13:32:19 (2 weeks ago)

Author:

232012

Comment:

--

AdvancedReports

@@ -10 +10 @@
 
 {{{
-SQL CODE
+WITH product_sales_yearly AS (
+    SELECT 
+        op.product_id,
+        SUM(op.quantity) AS total_sold_yearly,
+        
+        SUM(op.quantity) / 365.0 AS daily_sales_velocity
+    FROM project.ORDER_PRODUCTS op
+    JOIN project.ORDERS o ON op.order_id = o.order_id
+    
+    WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '1 year'
+      AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
+    GROUP BY op.product_id
+),
+inventory_velocity AS (
+    SELECT 
+        p.product_id,
+        p.format,
+        p.stock,
+        p.price,
+        r.title AS release_title,
+        psy.total_sold_yearly,
+        psy.daily_sales_velocity,
+        
+        CASE 
+            WHEN psy.daily_sales_velocity > 0 THEN p.stock / psy.daily_sales_velocity
+            ELSE 9999 
+        END AS days_until_out_of_stock
+    FROM project.PRODUCTS p
+    JOIN project.RELEASES r ON p.release_id = r.release_id
+    JOIN product_sales_yearly psy ON p.product_id = psy.product_id
+)
+SELECT 
+    product_id,
+    release_title,
+    format,
+    stock AS current_stock,
+    total_sold_yearly,
+    ROUND(daily_sales_velocity, 4) AS daily_velocity,
+    ROUND(days_until_out_of_stock, 1) AS days_left,
+    
+    CEIL((daily_sales_velocity * 90) - stock) AS recommended_restock_quantity,
+    
+    ROUND(CEIL((daily_sales_velocity * 90) - stock) * price, 2) AS estimated_restock_cost
+FROM inventory_velocity
+WHERE days_until_out_of_stock < 30
+ORDER BY daily_velocity DESC, days_left ASC;
 }}}