zainanisa

-- 1. Sell-Through Rate, Dead Stock and Days to Sell by Category
SELECT
  product_category,
  EXTRACT(YEAR FROM created_at) AS year,
  EXTRACT(MONTH FROM created_at) AS month,
  FORMAT_DATE('%B', DATE(created_at)) AS month_name,

  COUNT(id) AS total_stocked,
  COUNTIF(sold_at IS NOT NULL) AS units_sold,
  COUNTIF(sold_at IS NULL) AS dead_stock_units,

zainanisa

-- RFM (Recency, Frequency, Monetary) ver. 1
-- 1. Build the RFM Base Table
WITH rfm_base AS (
  SELECT
    oi.user_id,
    DATE_DIFF(
      DATE '2026-01-01', 
      MAX(DATE(oi.created_at)),
      DAY
    ) + 1                                    AS recency,

zainanisa

-- 1. Total Revenue & Gross Profit Margin by Month and Year
-- Profit Margin = (Revenue - COGS) / Revenue
-- Revenue = price * qty product
-- COGS = cost * qty product
SELECT 
  ii.product_category,
  EXTRACT(year FROM oi.created_at) AS year,
  EXTRACT(month FROM oi.created_at) AS month,
  FORMAT_DATE('%B', DATE(oi.created_at)) AS month_name,