Context Navigation

Changes between Version 10 and Version 11 of AdvancedReports

Timestamp:: 06/02/26 21:53:23 (9 days ago)
Author:: 232012
Comment:: --

Legend:

: Unmodified
: Added
: Removed
: Modified

AdvancedReports

-              v10
+              v11
 {{{
+SET search_path TO project;
 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
+    FROM ORDER_PRODUCTS op
+    JOIN ORDERS o ON op.order_id = o.order_id
     WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '1 year'
 …
             ELSE 9999
         END AS days_until_out_of_stock
+    FROM project.PRODUCTS p
+    JOIN project.RELEASES r ON p.release_id = r.release_id
+    FROM PRODUCTS p
+    JOIN RELEASES r ON p.release_id = r.release_id
     JOIN product_sales_yearly psy ON p.product_id = psy.product_id
+)
 …
     stock AS current_stock,
     total_sold_yearly,
+    ROUND(CAST(daily_sales_velocity AS NUMERIC), 4) AS daily_velocity,
+    ROUND(CAST(daily_sales_velocity AS NUMERIC), 2) AS daily_velocity,
     ROUND(CAST(days_until_out_of_stock AS NUMERIC), 1) AS days_left,
+    CEIL((daily_sales_velocity * 90) - stock) AS recommended_restock_quantity,
+    ROUND((CEIL((daily_sales_velocity * 90) - stock) * price AS NUMERIC), 2) AS estimated_restock_cost
+    CEIL((daily_sales_velocity * 90) - stock) AS recommended_restock_quantity
 FROM inventory_velocity
 WHERE days_until_out_of_stock < 30
 …
 {{{
+RELATIONAL ALGEBRA
+ProductSalesYearly <-
+γ product_id;
+  total_sold_yearly := SUM(op.quantity),
+  daily_sales_velocity := SUM(op.quantity) / 365.0
+(
+  σ o.purchase_date >= CURRENT_DATE - 1 year
+    ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
+  (
+    ORDER_PRODUCTS op ⨝ (op.order_id = o.order_id) ORDERS o
+  )
+)
+InventoryVelocity <-
+π product_id, format, stock, price, release_title,
+  total_sold_yearly, daily_sales_velocity,
+  days_until_out_of_stock :=
+    CASE
+      WHEN daily_sales_velocity > 0
+        THEN stock / daily_sales_velocity
+      ELSE 9999
+    END
+(
+  (
+    PRODUCTS p ⨝ (p.release_id = r.release_id) RELEASES r
+  )
+  ⨝ (p.product_id = psy.product_id) ProductSalesYearly psy
+)
+Result <-
+π product_id,
+  release_title,
+  format,
+  current_stock := stock,
+  total_sold_yearly,
+  daily_velocity := ROUND(daily_sales_velocity, 4),
+  days_left := ROUND(days_until_out_of_stock, 1),
+  recommended_restock_quantity :=
+    CEIL((daily_sales_velocity * 90) - stock)
+(
+  σ days_until_out_of_stock < 30
+  (
+    InventoryVelocity
+  )
+)
+τ daily_velocity ↓, days_left ↑ (Result)
 }}}
 …
 {{{
+SET search_path TO project;
 WITH product_sales_6months AS (
     SELECT
 …
         SUM(op.quantity) AS units_sold_6m,
         MAX(o.purchase_date) AS last_purchase_date
+    FROM project.ORDER_PRODUCTS op
+    JOIN project.ORDERS o ON op.order_id = o.order_id
+    FROM ORDER_PRODUCTS op
+    JOIN ORDERS o ON op.order_id = o.order_id
     WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '6 months'
       AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
 …
         product_id,
         COUNT(wishlist_id) AS wishlist_addition_count
+    FROM project.WISHLIST_PRODUCTS
+    FROM WISHLIST_PRODUCTS
     GROUP BY product_id
+)
 …
     p.price AS current_unit_price,
     (p.stock * p.price) AS frozen_capital,
     COALESCE(TO_CHAR(ps.last_purchase_date, 'YYYY-MM-DD'), 'NEVER BOUGHT') AS last_sold_date,
     COALESCE(pw.wishlist_addition_count, 0) AS times_on_wishlists,
     CASE
         WHEN COALESCE(pw.wishlist_addition_count, 0) > 0 THEN 'Discount Target (Wishlisted)'
         ELSE 'Deep Liquidation/Clearance Target'
     END AS inventory_action_plan
+FROM project.PRODUCTS p
+JOIN project.RELEASES r ON p.release_id = r.release_id
+FROM PRODUCTS p
+JOIN RELEASES r ON p.release_id = r.release_id
 LEFT JOIN product_sales_6months ps ON p.product_id = ps.product_id
 LEFT JOIN product_wishlist_counts pw ON p.product_id = pw.product_id
 WHERE (ps.product_id IS NULL) AND (p.stock > 0)
 ORDER BY frozen_capital DESC, times_on_wishlists DESC;
 …
 {{{
+RELATIONAL ALGEBRA
+ProductSales6M <-
+γ product_id;
+  units_sold_6m := SUM(op.quantity),
+  last_purchase_date := MAX(o.purchase_date)
+(
+  σ o.purchase_date >= NOW - 6 months
+    ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
+  (
+    ORDER_PRODUCTS op ⨝ (op.order_id = o.order_id) ORDERS o
+  )
+)
+WishlistCounts <-
+γ product_id;
+  wishlist_addition_count := COUNT(wishlist_id)
+(
+  WISHLIST_PRODUCTS
+)
+DeadStockAnalysis <-
+π p.product_id,
+  r.title,
+  p.format,
+  unsold_stock_quantity := p.stock,
+  current_unit_price := p.price,
+  frozen_capital := p.stock * p.price,
+  last_sold_date := COALESCE(ps.last_purchase_date, 'NEVER BOUGHT'),
+  times_on_wishlists := COALESCE(wc.wishlist_addition_count, 0),
+  inventory_action_plan :=
+    CASE
+      WHEN COALESCE(wc.wishlist_addition_count, 0) > 0
+        THEN 'Discount Target (Wishlisted)'
+      ELSE 'Deep Liquidation/Clearance Target'
+    END
+(
+  (
+    PRODUCTS p ⨝ (p.release_id = r.release_id) RELEASES r
+  )
+  ⟕ ProductSales6M ps
+  ⟕ WishlistCounts wc
+)
+Result <-
+σ ps.product_id IS NULL ∧ p.stock > 0
+(
+  DeadStockAnalysis
+)
 }}}
 …
 {{{
+SET search_path TO project;
 WITH discount_events AS (
     SELECT
 …
         m.discount AS discount_percentage,
         mp.product_id
+    FROM project.MODIFICATIONS m
+    JOIN project.MODIFICATION_PRODUCTS mp ON m.modification_id = mp.modification_id
+    FROM MODIFICATIONS m
+    JOIN MODIFICATION_PRODUCTS mp ON m.modification_id = mp.modification_id
     WHERE m.type_of_modification = 'DISCOUNT'
 ),
 …
         de.modification_id,
         de.product_id,
         COALESCE(SUM(op.quantity), 0) AS units_sold_before,
         COALESCE(SUM(op.quantity * op.price_at_purchase), 0.00) AS revenue_before
     FROM discount_events de
     LEFT JOIN project.ORDER_PRODUCTS op ON de.product_id = op.product_id
     LEFT JOIN project.ORDERS o ON op.order_id = o.order_id
+    LEFT JOIN ORDER_PRODUCTS op ON de.product_id = op.product_id
+    LEFT JOIN ORDERS o ON op.order_id = o.order_id
         AND o.purchase_date >= de.date_modified - INTERVAL '30 days'
         AND o.purchase_date < de.date_modified
 …
         COALESCE(SUM(op.quantity), 0) AS units_sold_after,
         COALESCE(SUM(op.quantity * op.price_at_purchase), 0.00) AS revenue_after
     FROM discount_events de
     LEFT JOIN project.ORDER_PRODUCTS op ON de.product_id = op.product_id
     LEFT JOIN project.ORDERS o ON op.order_id = o.order_id
+    LEFT JOIN ORDER_PRODUCTS op ON de.product_id = op.product_id
+    LEFT JOIN ORDERS o ON op.order_id = o.order_id
         AND o.purchase_date > de.date_modified
         AND o.purchase_date <= de.date_modified + INTERVAL '30 days'
 …
         post.revenue_after,
         (post.revenue_after - pre.revenue_before) AS net_revenue_impact
     FROM discount_events de
+    JOIN project.PRODUCTS p ON de.product_id = p.product_id
+    JOIN project.RELEASES r ON p.release_id = r.release_id
+    JOIN pre_promo_sales pre ON de.modification_id = pre.modification_id AND de.product_id = pre.product_id
+    JOIN post_promo_sales post ON de.modification_id = post.modification_id AND de.product_id = post.product_id
+    JOIN PRODUCTS p ON de.product_id = p.product_id
+    JOIN RELEASES r ON p.release_id = r.release_id
+    JOIN pre_promo_sales pre
+        ON de.modification_id = pre.modification_id
+       AND de.product_id = pre.product_id
+    JOIN post_promo_sales post
+        ON de.modification_id = post.modification_id
+       AND de.product_id = post.product_id
+)
 SELECT
 …
     revenue_after,
     net_revenue_impact,
     CASE
         WHEN (net_revenue_impact > 0) AND (volume_change > 0) THEN 'SUCCESS: Volume generated profit'
 …
         ELSE 'NEUTRAL'
     END AS promotion_verdict
 FROM promo_summary
 ORDER BY promotion_start_date DESC, net_revenue_impact DESC;
 …
 {{{
+RELATIONAL ALGEBRA
+DiscountEvents <-
+σ m.type_of_modification = 'DISCOUNT'
+(
+  MODIFICATIONS m ⨝ (m.modification_id = mp.modification_id) MODIFICATION_PRODUCTS mp
+)
+PrePromoSales <-
+γ modification_id, product_id;
+  units_sold_before := SUM(op.quantity),
+  revenue_before := SUM(op.quantity * op.price_at_purchase)
+(
+  σ o.purchase_date ∈ [m.date_modified - 30 days, m.date_modified)
+    ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
+  (
+    DiscountEvents de ⨝ ORDER_PRODUCTS op ⨝ ORDERS o
+  )
+)
+PostPromoSales <-
+γ modification_id, product_id;
+  units_sold_after := SUM(op.quantity),
+  revenue_after := SUM(op.quantity * op.price_at_purchase)
+(
+  σ o.purchase_date ∈ (m.date_modified, m.date_modified + 30 days]
+    ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
+  (
+    DiscountEvents de ⨝ ORDER_PRODUCTS op ⨝ ORDERS o
+  )
+)
+PromoSummary <-
+π product_id,
+  release_title := r.title,
+  product_format := p.format,
+  promotion_start_date := de.date_modified,
+  discount_applied := de.discount_percentage,
+  volume_change := post.units_sold_after - pre.units_sold_before,
+  net_revenue_impact := post.revenue_after - pre.revenue_before,
+  units_sold_before := pre.units_sold_before,
+  units_sold_after := post.units_sold_after,
+  revenue_before := pre.revenue_before,
+  revenue_after := post.revenue_after
+(
+  DiscountEvents de
+  ⨝ PRODUCTS p
+  ⨝ RELEASES r
+  ⨝ PrePromoSales pre
+  ⨝ PostPromoSales post
+)
+Result <-
+τ promotion_start_date ↓, net_revenue_impact ↓
+(
+  π *,
+    promotion_verdict :=
+      CASE
+        WHEN net_revenue_impact > 0 ∧ volume_change > 0
+          THEN 'SUCCESS'
+        WHEN net_revenue_impact < 0 ∧ volume_change > 0
+          THEN 'MARGIN LOSS'
+        WHEN volume_change <= 0
+          THEN 'FAILURE'
+        ELSE 'NEUTRAL'
+      END
+  (
+    PromoSummary
+  )
+)
 }}}
 …
 {{{
+SET search_path TO project;
 WITH user_purchase_metrics AS (
     SELECT
 …
         COALESCE(SUM(o.points_used), 0) AS lifetime_points_burned,
         COALESCE(SUM(op.quantity * op.price_at_purchase), 0.00) AS total_monetary_spend
+    FROM project.ORDERS o
+    LEFT JOIN project.ORDER_PRODUCTS op ON o.order_id = op.order_id
+    FROM ORDERS o
+    LEFT JOIN ORDER_PRODUCTS op ON o.order_id = op.order_id
     WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '2 years'
       AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
 …
         COALESCE(upm.lifetime_points_earned, 0) AS points_earned_2yr,
         COALESCE(upm.lifetime_points_burned, 0) AS points_burned_2yr
+    FROM project.CONSUMERS c
+    JOIN project.USERS u ON c.user_id = u.user_id
+    FROM CONSUMERS c
+    JOIN USERS u ON c.user_id = u.user_id
     LEFT JOIN user_purchase_metrics upm ON c.user_id = upm.user_id
     WHERE u.date_created >= CURRENT_DATE - INTERVAL '2 years'
 …
     points_earned_2yr,
     points_burned_2yr,
     CASE
         WHEN is_vip = 1 THEN 'VIP Tier: High Future Discount Liability'
 …
         ELSE 'Standard Active Engagement Profile'
     END AS customer_retention_segment
 FROM promo_summary_with_flags
 ORDER BY account_creation_vintage DESC, current_unspent_points_balance DESC;
 …
 {{{
+RELATIONAL ALGEBRA
+}}}
+UserPurchaseMetrics <-
+γ user_id;
+  total_orders_placed := COUNT(order_id),
+  lifetime_points_earned := SUM(points_earned),
+  lifetime_points_burned := SUM(points_used),
+  total_monetary_spend := SUM(op.quantity * op.price_at_purchase)
+(
+  σ o.purchase_date >= NOW - 2 years
+    ∧ o.status ∈ {PAID, SHIPPED, DELIVERED}
+  (
+    ORDERS o ⟕ ORDER_PRODUCTS op
+  )
+)
+PromoSummaryUsers <-
+π u.user_id,
+  u.username,
+  u.email,
+  account_creation_vintage := EXTRACT(YEAR FROM u.date_created),
+  registration_date := u.date_created,
+  orders_count_2yr := COALESCE(um.total_orders_placed, 0),
+  lifetime_spend_amount := COALESCE(um.total_monetary_spend, 0),
+  current_unspent_points_balance := c.points_collected,
+  points_earned_2yr := COALESCE(um.lifetime_points_earned, 0),
+  points_burned_2yr := COALESCE(um.lifetime_points_burned, 0)
+(
+  CONSUMERS c ⨝ USERS u ⟕ UserPurchaseMetrics um
+)
+UserFlags <-
+π *,
+  is_vip :=
+    CASE WHEN current_unspent_points_balance ≥ 2000 ∧ orders_count_2yr ≥ 10 THEN 1 ELSE 0 END,
+  is_hoarder :=
+    CASE WHEN current_unspent_points_balance ≥ 1000 ∧ points_burned_2yr = 0 THEN 1 ELSE 0 END,
+  is_churned :=
+    CASE WHEN points_burned_2yr > 0 ∧ orders_count_2yr ≤ 2 THEN 1 ELSE 0 END
+(
+  PromoSummaryUsers
+)
+Result <-
+τ account_creation_vintage ↓, current_unspent_points_balance ↓
+(
+  π user_id, username, email,
+    account_creation_vintage,
+    registration_date,
+    orders_count_2yr,
+    lifetime_spend_amount,
+    current_unspent_points_balance,
+    points_earned_2yr,
+    points_burned_2yr,
+    customer_retention_segment :=
+      CASE
+        WHEN is_vip = 1 THEN 'VIP'
+        WHEN is_hoarder = 1 THEN 'HOARDER'
+        WHEN is_churned = 1 THEN 'CHURNED'
+        ELSE 'STANDARD'
+      END
+  (
+    UserFlags
+  )
+)
+}}}