assignments(user_id, variant, assigned_at) and purchases(user_id, purchased_at, revenue)

For users assigned during September 2026 (each user appears once), return per variant: how many users were assigned, how many purchased within 14 days of assignment, the conversion rate, and the average 14-day revenue per assigned user rather than per converter.

A purchase counts when purchased_at falls in [assigned_at, assigned_at + 14 days].

Return variant, users, converters, conversion_rate and avg_revenue_per_user, the last two rounded to two decimals, ordered by variant.

Dividing total revenue by the number of converters is the classic mistake: it makes a variant that converts rarely but richly look like the winner.

Tables

assignments

user_id  variant  assigned_at
-------  -------  -------------------
1        A        2026-09-01 10:00:00
2        A        2026-09-01 10:00:00
3        B        2026-09-01 10:00:00
4        B        2026-09-01 10:00:00
5        B        2026-09-01 10:00:00
6        A        2026-08-15 10:00:00

purchases

user_id  purchased_at         revenue
-------  -------------------  -------
1        2026-09-03 10:00:00  20.00
2        2026-09-05 10:00:00  20.00
3        2026-09-04 10:00:00  200.00
5        2026-09-21 10:00:00  500.00
6        2026-08-16 10:00:00  999.00

Expected result

variant  users  converters  conversion_rate  avg_revenue_per_user
-------  -----  ----------  ---------------  --------------------
A        2      2           100.00           20.00
B        3      1           33.33            66.67