Structuring Ambiguous Business Problems Questions

WITH params AS (
  SELECT '2025-01'::text AS month_a, '2025-02'::text AS month_b
),
orders_filtered AS (
  SELECT o.order_id, o.user_id, o.order_total, to_char(o.order_date, 'YYYY-MM') AS month
  FROM orders o
  JOIN params p ON to_char(o.order_date, 'YYYY-MM') IN (p.month_a, p.month_b)
),
order_items AS (
  SELECT oi.order_id, oi.item_id, oi.category,
         (oi.price * oi.quantity) AS item_rev
  FROM items oi
  WHERE oi.order_id IN (SELECT order_id FROM orders_filtered)
),
order_item_sums AS (
  -- sum item revenue by order and category, and total item revenue per order
  SELECT
    oi.order_id,
    oi.category,
    SUM(oi.item_rev) AS cat_item_rev
  FROM order_items oi
  GROUP BY oi.order_id, oi.category
),
order_total_item_rev AS (
  SELECT
    o.order_id,
    SUM(oi.price * oi.quantity) AS total_item_rev
  FROM items oi
  JOIN orders_filtered o ON o.order_id = oi.order_id
  GROUP BY o.order_id
),
order_category_alloc AS (
  -- allocate order_total to categories proportionally to item revenue
  SELECT
    of.order_id,
    of.month,
    s.category,
    (s.cat_item_rev / NULLIF(t.total_item_rev,0)) * of.order_total AS allocated_revenue
  FROM orders_filtered of
  JOIN order_item_sums s ON s.order_id = of.order_id
  LEFT JOIN order_total_item_rev t ON t.order_id = of.order_id
),
order_promos AS (
  SELECT DISTINCT order_id, TRUE AS has_promo
  FROM promotions
),
order_category_promo AS (
  SELECT
    a.order_id,
    a.month,
    a.category,
    COALESCE(p.has_promo, FALSE) AS has_promo,
    a.allocated_revenue,
    -- count each order once per category; later compute order counts by distinct order_id
    1 as order_count_1
  FROM order_category_alloc a
  LEFT JOIN order_promos p ON p.order_id = a.order_id
),
agg_by_group AS (
  SELECT
    month,
    category,
    has_promo,
    COUNT(DISTINCT order_id) AS orders_in_group,
    SUM(allocated_revenue) AS revenue_in_group
  FROM order_category_promo
  GROUP BY month, category, has_promo
),
final_metrics AS (
  SELECT
    g.*,
    (revenue_in_group::numeric / NULLIF(orders_in_group,0)) AS avg_order_value_group
  FROM agg_by_group g
),
month_totals AS (
  SELECT month,
    SUM(revenue_in_group) AS total_revenue,
    SUM(orders_in_group) AS total_orders
  FROM agg_by_group
  GROUP BY month
)
SELECT
  f.category,
  f.has_promo,
  f.month,
  f.orders_in_group,
  f.revenue_in_group,
  ROUND(f.avg_order_value_group,2) AS group_aov,
  ROUND((f.orders_in_group::numeric / mt.total_orders)::numeric,4) AS order_share,
  -- contribution = group AOV * order share (so sum of contributions = overall AOV)
  ROUND((f.avg_order_value_group * (f.orders_in_group::numeric / mt.total_orders))::numeric,4) AS contribution_to_aov
FROM final_metrics f
JOIN month_totals mt ON mt.month = f.month
ORDER BY category, has_promo DESC, month;

WITH first_session_on_signup AS (
  -- Find first session for each user on their signup_date to capture initial app_version/device_type
  SELECT
    u.user_id,
    u.signup_date::date AS signup_date,
    s.app_version,
    s.device_type,
    ROW_NUMBER() OVER (PARTITION BY u.user_id ORDER BY s.started_at) AS rn
  FROM users u
  LEFT JOIN sessions s
    ON s.user_id = u.user_id
    AND DATE(s.started_at) = u.signup_date::date
  WHERE u.signup_date IS NOT NULL
),
cohort AS (
  -- Keep only users who had at least one session on signup day (otherwise assign unknown app/device)
  SELECT
    user_id,
    signup_date,
    COALESCE(app_version, 'unknown') AS app_version,
    COALESCE(device_type, 'unknown') AS device_type
  FROM first_session_on_signup
  WHERE rn = 1
),
cohort_sizes AS (
  SELECT
    signup_date,
    app_version,
    device_type,
    COUNT(DISTINCT user_id) AS cohort_users
  FROM cohort
  GROUP BY signup_date, app_version, device_type
),
activity_day7 AS (
  -- Users from cohort who had any session exactly 7 days after signup
  SELECT
    c.signup_date,
    c.app_version,
    c.device_type,
    COUNT(DISTINCT s.user_id) AS active_day7_users
  FROM cohort c
  JOIN sessions s
    ON s.user_id = c.user_id
    AND DATE(s.started_at) = c.signup_date + INTERVAL '7 day'
  GROUP BY c.signup_date, c.app_version, c.device_type
)
SELECT
  cs.signup_date,
  cs.app_version,
  cs.device_type,
  cs.cohort_users,
  COALESCE(ad7.active_day7_users, 0) AS active_day7_users,
  ROUND( (COALESCE(ad7.active_day7_users, 0)::decimal / cs.cohort_users) * 100, 2) AS retention_day7_pct
FROM cohort_sizes cs
LEFT JOIN activity_day7 ad7
  ON cs.signup_date = ad7.signup_date
  AND cs.app_version = ad7.app_version
  AND cs.device_type = ad7.device_type
ORDER BY cs.signup_date DESC, cs.app_version, cs.device_type;