E-Commerce Inventory Optimization and Retention Rate Analysis 🛒

with 
step1 as (
  SELECT
    p.category
    , COALESCE(SUM(oi.sale_price), 0) as revenue
  FROM `sql-project-376612.thelook_ecommerce.order_items` oi
  JOIN `sql-project-376612.thelook_ecommerce.products` p
  ON oi.product_id = p.id
  JOIN `sql-project-376612.thelook_ecommerce.orders` o
  ON oi.order_id = o.order_id
  WHERE DATE(o.created_at) BETWEEN '2021-01-01' AND '2021-12-31'
  AND lower(o.status) = 'complete'
  GROUP BY 1
)
, step2 as (
  SELECT
    p.category
    , COALESCE(SUM(oi.sale_price), 0) as revenue
  FROM `sql-project-376612.thelook_ecommerce.order_items` oi
  JOIN `sql-project-376612.thelook_ecommerce.products` p
  ON oi.product_id = p.id
  JOIN `sql-project-376612.thelook_ecommerce.orders` o
  ON oi.order_id = o.order_id
  WHERE DATE(o.created_at) BETWEEN '2022-01-01' AND '2022-12-31'
  AND lower(o.status) = 'complete'
  GROUP BY 1 
)

SELECT
  step1.category
  , step1.revenue as revenue_2021
  , step2.revenue as revenue_2022
  , CAST((step2.revenue - step1.revenue) as decimal) / step1.revenue * 100 as Growth
FROM step1 
FULL OUTER JOIN step2
ON step1.category = step2.category
order by 4

WITH
aggregated_view AS (
  SELECT
    oi.user_id 
    , oi.order_id
    , oi.created_at
  FROM `sql-project-376612.thelook_ecommerce.order_items` oi
  JOIN `sql-project-376612.thelook_ecommerce.products` p
  ON oi.product_id = p.id
  WHERE p.category = DESIRED CATEGORY
  AND lower(oi.status) = 'complete'
  AND EXTRACT(YEAR FROM oi.created_at) IN (2022)
)
, cohort_items AS (
  SELECT
    user_id
    , MIN(DATE(DATE_TRUNC(created_at,MONTH))) AS cohort_month
  FROM aggregated_view
  GROUP BY 1
)
, user_activities AS (
  SELECT
    av.user_id
    , DATE_DIFF(
        DATE(DATE_TRUNC(av.created_at,MONTH)),
        cohort.cohort_month,
        MONTH
    ) AS month_number
  FROM aggregated_view av
  LEFT JOIN cohort_items cohort ON av.user_id = cohort.user_id
  WHERE EXTRACT(YEAR FROM cohort.cohort_month) IN (2022)
  GROUP BY 1, 2
)
, cohort_size AS (
  SELECT
    cohort_month
    , count(1) AS num_users
  FROM cohort_items
  GROUP BY 1
  ORDER BY 1
)

, retention_table AS (
  SELECT
    c.cohort_month
    , a.month_number
    , COUNT(1) AS num_users
  FROM user_activities a
  LEFT JOIN cohort_items c
    ON a.user_id = c.user_id
  GROUP BY 1, 2
)

-- our final value: (cohort_month, size, month_number, percentage)
SELECT
  retention_table.cohort_month
  , cohort_size.num_users AS cohort_size
  , retention_table.month_number
  , retention_table.num_users AS total_users
  , CAST(retention_table.num_users AS decimal)/ cohort_size.num_users * 100 AS percentage
FROM retention_table
LEFT JOIN cohort_size
  ON retention_table.cohort_month = cohort_size.cohort_month
WHERE retention_table.cohort_month IS NOT NULL
ORDER BY 1, 3