= Complex DB Reports

== 1. Top Selling Products and Restock Plan

{{{#!div style="text-align: justify; width: 100%;"
This report is meant to look at the best-selling items over the last year to figure out exactly when they will sell out and how products should be repurchased. By calculating how many copies of a specific vinyl, CD, or cassette are sold every day and comparing that speed to what is left in stock, the query will find items that will run out of stock in less than a month. It then automatically calculates the perfect order amount to keep the store supplied for the next three months without overspending, ensuring top money-makers are never missing from the shelves.
}}}

==== SQL

{{{
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 ORDER_PRODUCTS op
    JOIN 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 PRODUCTS p
    JOIN 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(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

FROM inventory_velocity
WHERE days_until_out_of_stock < 30
ORDER BY daily_velocity DESC, days_left ASC;
}}}

==== 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, 2),
  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)
}}}

== 2. Slow Moving Products

{{{#!div style="text-align: justify; width: 100%;"
This report is meant to look at items that are completely stuck in inventory. The query scans the database to find products that have either never been bought or have had zero sales for more than six months, while also checking if customers are still adding them to their wishlists or ignoring them completely. It multiplies the current unsold stock by the item's original price to show managers exactly how much cash is frozen in dead inventory, making it easy to see which releases need discounts.
}}}

==== SQL

{{{
SET search_path TO project;

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 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')
    GROUP BY op.product_id
),
product_wishlist_counts AS (
    SELECT 
        product_id,
        COUNT(wishlist_id) AS wishlist_addition_count

    FROM 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 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 >= CURRENT_DATE - 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
)

JoinedData <-
(
  (
    PRODUCTS p ⨝ (p.release_id = r.release_id) RELEASES r
  )
  ⟕ (p.product_id = ps.product_id) ProductSales6M ps
  ⟕ (p.product_id = wc.product_id) WishlistCounts wc
)

Result <-
π p.product_id,
  release_title := r.title,
  p.format,
  unsold_stock_quantity := p.stock,
  current_unit_price := p.price,
  frozen_capital := p.stock * p.price,
  last_sold_date := COALESCE(TO_CHAR(ps.last_purchase_date, 'YYYY-MM-DD'), '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
(
  σ ps.product_id IS NULL ∧ p.stock > 0
  (
    JoinedData
  )
)

τ frozen_capital ↓, times_on_wishlists ↓ (Result)
}}}

== 3. Impact of Admin Discounts on Sales Numbers

{{{#!div style="text-align: justify; width: 100%;"
This report checks if discounts and promotions created by product managers actually bring in more profit or just lose money. It monitors sales numbers 30 days before and 30 days after an administrator modifies a product to lower its price. By comparing the drop in profit margin per item against the increase in total orders, the query would prove whether the discount caused a big enough wave of new buyers to make the promotion successful or if it just hurt the overall revenue.
}}}

==== SQL

{{{
SET search_path TO project;

WITH discount_events AS (
    SELECT 
        m.modification_id,
        m.admin_id,
        m.date_modified,
        m.discount AS discount_percentage,
        mp.product_id

    FROM MODIFICATIONS m
    JOIN 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 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
        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 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'
        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 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 
    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;
}}}

==== 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
  )
)
}}}



== 4. Customer Habits and Points Spending

{{{#!div style="text-align: justify; width: 100%;"
This report tracks how registered users behave over a period of two years by looking at how they earn and spend their rewards points. It groups buyers based on the year they created their accounts and measures their total shopping history alongside their points balance to see who is saving up thousands of points and who is actively spending them. This helps find high-value users who hold a large number of unspent points, which represents a future discount cost and can indicate if customers stop buying completely once their free rewards are used up.
}}}

==== SQL

{{{
SET search_path TO project;

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 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')
    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 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'
),
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

{{{
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
  )
)
}}}