Changes between Version 9 and Version 10 of AdvancedReports

Timestamp:

05/31/26 16:16:04 (12 days ago)

Author:

232012

Comment:

--

AdvancedReports

@@ -73 +73 @@
 
 {{{
-SQL CODE
+WITH product_sales_6months AS (
+    SELECT 
+        op.product_id,
+        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
+    WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '6 months'
+      AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
+    GROUP BY op.product_id
+),
+product_wishlist_counts AS (
+    SELECT 
+        product_id,
+        COUNT(wishlist_id) AS wishlist_addition_count
+    FROM project.WISHLIST_PRODUCTS
+    GROUP BY product_id
+)
+SELECT 
+    p.product_id,
+    r.title AS release_title,
+    p.format,
+    p.stock AS unsold_stock_quantity,
+    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
+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;
 }}}
 
@@ -91 +127 @@
 
 {{{
-SQL CODE
+WITH discount_events AS (
+    SELECT 
+        m.modification_id,
+        m.admin_id,
+        m.date_modified,
+        m.discount AS discount_percentage,
+        mp.product_id
+    FROM project.MODIFICATIONS m
+    JOIN project.MODIFICATION_PRODUCTS mp ON m.modification_id = mp.modification_id
+    WHERE m.type_of_modification = 'DISCOUNT'
+),
+pre_promo_sales AS (
+    SELECT 
+        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 
+        AND o.purchase_date >= de.date_modified - INTERVAL '30 days'
+        AND o.purchase_date < de.date_modified
+        AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
+    GROUP BY de.modification_id, de.product_id
+),
+post_promo_sales AS (
+    SELECT 
+        de.modification_id,
+        de.product_id,
+        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 
+        AND o.purchase_date > de.date_modified
+        AND o.purchase_date <= de.date_modified + INTERVAL '30 days'
+        AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
+    GROUP BY de.modification_id, de.product_id
+),
+promo_summary AS (
+    SELECT 
+        de.product_id,
+        r.title AS release_title,
+        p.format AS product_format,
+        de.date_modified AS promotion_start_date,
+        de.discount_percentage AS discount_applied,
+        pre.units_sold_before,
+        post.units_sold_after,
+        (post.units_sold_after - pre.units_sold_before) AS volume_change,
+        pre.revenue_before,
+        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
+)
+SELECT 
+    product_id,
+    release_title,
+    product_format,
+    promotion_start_date,
+    discount_applied,
+    units_sold_before,
+    units_sold_after,
+    volume_change,
+    revenue_before,
+    revenue_after,
+    net_revenue_impact,
+    CASE 
+        WHEN (net_revenue_impact > 0) AND (volume_change > 0) THEN 'SUCCESS: Volume generated profit'
+        WHEN (net_revenue_impact < 0) AND (volume_change > 0) THEN 'MARGIN LOSS: Volume rose but lost overall revenue'
+        WHEN (volume_change <= 0) THEN 'FAILURE: No demand increase observed'
+        ELSE 'NEUTRAL'
+    END AS promotion_verdict
+FROM promo_summary
+ORDER BY promotion_start_date DESC, net_revenue_impact DESC;
 }}}
 
@@ -111 +224 @@
 
 {{{
-SQL CODE
-}}}
-
-==== Relational Algebra
-
-{{{
-RELATIONAL ALGEBRA
-}}}
-
-
+WITH user_purchase_metrics AS (
+    SELECT 
+        o.user_id,
+        COUNT(o.order_id) AS total_orders_placed,
+        COALESCE(SUM(o.points_earned), 0) AS lifetime_points_earned,
+        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
+    WHERE o.purchase_date >= CURRENT_DATE - INTERVAL '2 years'
+      AND o.status IN ('PAID', 'SHIPPED', 'DELIVERED')
+    GROUP BY o.user_id
+),
+promo_summary AS (
+    SELECT 
+        u.user_id,
+        u.username,
+        u.email,
+        EXTRACT(YEAR FROM u.date_created) AS account_creation_vintage,
+        u.date_created AS registration_date,
+        COALESCE(upm.total_orders_placed, 0) AS orders_count_2yr,
+        ROUND(COALESCE(upm.total_monetary_spend, 0.00), 2) AS lifetime_spend_amount,
+        c.points_collected AS current_unspent_points_balance,
+        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
+    LEFT JOIN user_purchase_metrics upm ON c.user_id = upm.user_id
+    WHERE u.date_created >= CURRENT_DATE - INTERVAL '2 years'
+),
+promo_summary_with_flags AS (
+    SELECT 
+        *,
+        CASE WHEN current_unspent_points_balance >= 2000 AND orders_count_2yr >= 10 THEN 1 ELSE 0 END AS is_vip,
+        CASE WHEN current_unspent_points_balance >= 1000 AND points_burned_2yr = 0 THEN 1 ELSE 0 END AS is_hoarder,
+        CASE WHEN points_burned_2yr > 0 AND orders_count_2yr <= 2 THEN 1 ELSE 0 END AS is_churned
+    FROM promo_summary
+)
+SELECT 
+    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,
+    CASE 
+        WHEN is_vip = 1 THEN 'VIP Tier: High Future Discount Liability'
+        WHEN is_hoarder = 1 THEN 'Points Hoarder: Inactive Burn (High Risk)'
+        WHEN is_churned = 1 THEN 'Churned After Reward Use (No Retention)'
+        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
+
+{{{
+RELATIONAL ALGEBRA
+}}}
+
+