Sample query using a temporary index table to calculate unique conversions by variation for visitors first seen in a given time window

sample_indexed_unique_conversion_query.sql

--Create a table indicating the first decision event for each user.
CREATE TABLE first_seen (userId varchar(64), first_seen DateTime);
CREATE INDEX first_seen_index ON first_seen (userId(64));

--Create index containing the relevant data.
CREATE INDEX pricing_index ON pricing (end_user_id(64), timestamp, event_name(100));

--Setup date parameters
SET @start_time = "2017-05-02 00:00:00";
SET @end_time = "2017-05-03 00:00:00";

TRUNCATE TABLE first_seen;

--Populate the 'table' with the first decision timestamps.
INSERT INTO first_seen (userId, first_seen)
select end_user_id, min(FROM_UNIXTIME(timestamp)) from pricing
where event_name = ""
and FROM_UNIXTIME(timestamp) between @start_time and @end_time
group by end_user_id;

--Count unique conversions and total conversions by event and variation.
select variation_id as `Variation`, event_name as `Event`, count(distinct end_user_id) as Uniques, count(*) as Totals
from (
    #Select all events for a user that occured on or after the first decision
    select p.end_user_id, p.timestamp, p.event_name, p.variation_id
        from first_seen f
        join pricing p on p.end_user_id = f.userId and p.timestamp >= f.first_seen
        where FROM_UNIXTIME(timestamp) < @end_time
        order by p.end_user_id, p.timestamp
        ) a
group by variation_id, event_name
order by event_name, variation_id;