Mercantail — Playtest Data Analysis

-- Session-level engagement summary
-- Identifies session length distribution and merge efficiency
SELECT
  session,
  player,
  ROUND(session_length_s / 60.0, 1)           AS duration_min,
  total_events,
  orders_fulfilled,
  energy_refills,
  ROUND(items_merged * 1.0 / NULLIF(items_spawned, 0), 2) AS merge_ratio,
  max_tier_reached,
  churn_signal,
  data_type
FROM session_summary
ORDER BY session_length_s DESC;

-- Identify sessions with churn signals and their characteristics
-- Churn defined as: session_ended before first energy gate OR no merges completed
-- Note: Churn_Signal in event_log marks friction shop_opens (S08/S10 only).
-- Session-level churn comes from session_summary.churn_signal
SELECT
  s.session,
  s.player,
  s.session_length_s,
  s.items_merged,
  s.max_tier_reached,
  s.churn_signal
FROM session_summary s
WHERE s.churn_signal = 'Y'
   OR s.session_length_s < 180  -- sessions under 3 min flagged for review
ORDER BY s.session_length_s;

-- Measure energy gate conversion: energy_refilled → shop_opened
-- Also captures voluntary vs friction-driven shop opens (Rich Data only)
SELECT
  e.session,
  COUNT(CASE WHEN e.event_type = 'energy_refilled' THEN 1 END) AS energy_refills,
  COUNT(CASE WHEN e.event_type = 'shop_opened'    THEN 1 END) AS shop_opens,
  COUNT(CASE WHEN e.event_type = 'shop_opened'
              AND e.shop_voluntary = 'Yes'           THEN 1 END) AS voluntary_opens,
  COUNT(CASE WHEN e.event_type = 'shop_opened'
              AND e.shop_voluntary = 'No'            THEN 1 END) AS friction_opens,
  e.data_type
FROM event_log e
GROUP BY e.session, e.data_type
ORDER BY e.session;

-- Tier progression funnel for Rich Data sessions (S08, S10)
-- Compares actual merge counts to 2:1 theoretical baseline
-- Efficiency < 100% indicates items consumed before reaching higher tiers
WITH tier_counts AS (
  SELECT
    session,
    1                                         AS tier,
    COUNT(CASE WHEN event_type = 'item_spawned' THEN 1 END) AS item_count
  FROM event_log WHERE session IN ('S08', 'S10')
  GROUP BY session
  UNION ALL
  SELECT session, tier_after, COUNT(*)
  FROM event_log
  WHERE session IN ('S08', 'S10')
    AND event_type = 'item_merged'
    AND tier_after BETWEEN 2 AND 5
  GROUP BY session, tier_after
)
SELECT
  tier,
  SUM(item_count)                                    AS actual_items,
  LAG(SUM(item_count)) OVER (ORDER BY tier) / 2    AS expected_items,
  ROUND(
    SUM(item_count) * 100.0 /
    (LAG(SUM(item_count)) OVER (ORDER BY tier) / 2), 0
  )                                                   AS efficiency_pct
FROM tier_counts
GROUP BY tier
ORDER BY tier;

-- Gold economy: earnings, spend, and net trajectory
-- gold_start = gold_before of first event (FIRST_VALUE by timestamp)
-- gold_end   = gold_before of session_ended event (final snapshot before close)
-- Rich Data sessions only (S08, S10)
SELECT
  session,
  FIRST_VALUE(gold_before) OVER (
    PARTITION BY session ORDER BY timestamp
  )                                                 AS gold_start,
  MAX(CASE WHEN event_type = 'session_ended'
        THEN gold_before END)                         AS gold_end,
  SUM(CASE WHEN event_type = 'order_fulfilled'
        THEN gold_after - gold_before ELSE 0 END)  AS gold_earned,
  SUM(CASE WHEN event_type = 'energy_refilled'
        THEN gold_before - gold_after ELSE 0 END)  AS gold_spent,
  ROUND(
    AVG(CASE WHEN event_type = 'order_fulfilled'
          THEN gold_after - gold_before END), 1
  )                                                 AS avg_gold_per_order,
  COUNT(CASE WHEN event_type = 'order_fulfilled'
          THEN 1 END)                              AS orders_fulfilled
FROM event_log
WHERE session IN ('S08', 'S10')
GROUP BY session;

-- Compare P08's two sessions across key engagement and economy metrics
-- No product change between sessions — measures player learning curve
SELECT
  session,
  ROUND(session_length_s / 60.0, 1)                      AS duration_min,
  orders_fulfilled,
  ROUND(orders_fulfilled * 60.0 / session_length_s, 2)   AS orders_per_min,
  energy_refills,
  ROUND(orders_fulfilled * 1.0 / energy_refills, 2)       AS orders_per_refill,
  merge_ratio,
  max_tier_reached,
  ROUND(events_per_minute, 1)                             AS events_per_min
FROM session_summary
WHERE player = 'P08'
ORDER BY session;