-- SQLite. Run only after the integrity checks in analysis.py.
-- Earliest assignment is fixed. Conflicting arms at the earliest timestamp fail QA.
-- Exact duplicate order rows are collapsed; conflicting records for one order ID fail QA.
-- This seven-day window is an explicit reconstruction assumption.
WITH first_assignment AS (
 SELECT client_id, arm, assigned_at FROM (
  SELECT *, ROW_NUMBER() OVER (
   PARTITION BY client_id ORDER BY assigned_at, event_id
  ) AS rn FROM assignments
 ) WHERE rn=1
), unique_orders AS (
 SELECT DISTINCT order_id, client_id, order_value, purchased_at FROM purchase_export
), orders_by_user AS (
 SELECT a.client_id, COUNT(o.order_id) AS orders, COALESCE(SUM(o.order_value),0) AS revenue
 FROM first_assignment a LEFT JOIN unique_orders o
 ON a.client_id=o.client_id
 AND o.purchased_at>=a.assigned_at
 AND o.purchased_at<datetime(a.assigned_at,'+7 days')
 GROUP BY a.client_id
)
SELECT a.arm, COUNT(*) AS users,
 SUM(CASE WHEN p.orders>0 THEN 1 ELSE 0 END) AS buyers,
 SUM(p.revenue) AS revenue, 1.0*SUM(p.revenue)/COUNT(*) AS rpu
FROM first_assignment a JOIN orders_by_user p ON p.client_id=a.client_id
GROUP BY a.arm ORDER BY a.arm;