ab.sql

-- Enforce a strongly typed schema on this stream
CREATE STREAM ab_event_stream
(
  name text,
  ab_group text,
  event_type varchar(1),
  cookie varchar(32)
);

CREATE CONTINUOUS VIEW ab_test_monitor AS
SELECT
  name, ab_group,
  sum(CASE WHEN event_type = 'v' THEN 1 ELSE 0 END) AS view_count,
  sum(CASE WHEN event_type = 'c' THEN 1 ELSE 0 END) AS conversion_count,
  count(DISTINCT cookie) AS uniques
FROM ab_event_stream GROUP BY name, ab_group;

-- Simulate some random event data. In reality, this could come from anywhere.
INSERT INTO ab_event_stream (name, ab_group, event_type, cookie)
  SELECT round(random() * 2) AS name, 
         round(random() * 4) AS ab_group,
         (CASE WHEN random() > 0.4 THEN 'v' ELSE 'c' END) AS event_type,
         md5(random()::text) AS cookie
  FROM generate_series(0, 100000);

-- Verify that traffic is being split properly between the A and B groups
SELECT ab_group, uniques FROM ab_test_monitor;

-- Compare the conversion rates between the A and B groups
SELECT ab_group,
  view_count * 100 / (conversion_count + view_count) AS conversion_rate
FROM ab_test_monitor;