tabswitch_testpilot_analysis.sql

-- data from https://testpilot.mozillalabs.com/testcases/tab-switch-study

CREATE TABLE tabopenclose (rowid MEDIUMINT NOT NULL AUTO_INCREMENT,user int, event_code varchar(20), id int, position int, window int, tab_site_hash int, srp int, tabs int, timestamp bigint, os varchar(128), ffversion varchar(20), PRIMARY KEY (rowid))

LOAD DATA LOCAL INFILE '~/tab_2000_users.csv'
    INTO TABLE tabopenclose 
    FIELDS TERMINATED BY ',' 
    LINES TERMINATED BY '\n' 
    (user, event_code, id, position, window, tab_site_hash, srp, tabs, timestamp, os, ffversion)

-- User and action frequencies
select t2.event_code, count(*) as N, count(distinct t1.user) as U, avg(t2.timestamp - t1.timestamp) as delta from tabopenclose t1, tabopenclose t2 where t1.user = t2.user and t1.window = t2.window and t1.rowid = t2.rowid -1 and t1.srp=1 and t2.srp <> 1 and t1.tab_site_hash <> t2.tab_site_hash group by t2.event_code ;

-- Sequence table
CREATE TABLE tabsequence (SELECT  t2.event_code as t2event, t1.event_code as t1event, t1.srp as srp1, t2.srp as srp2,  t1.tab_site_hash as hash1, t2.tab_site_hash as hash2,  t1.os, t2.ffversion, t1.position as position1, t2.position as position2, t1.rowid as r1, t2.rowid as r2, t2.timestamp - t1.timestamp as delta, t1.tabs as tabs1, t2.tabs as tabs2, t1.user as user from tabopenclose t1, tabopenclose t2  WHERE  t1.user = t2.user  and t1.window = t2.window  and t1.rowid = t2.rowid -1  and t2.timestamp - t1.timestamp < (30*60*1000) and t2.timestamp > t1.timestamp)

-- transition analysis
select count(*) as N, user , t1event, t2event, avg(delta) as avgtime, avg(position1-position2) as positionChange, avg(tabs1) as avgTabs, srp1,srp2  from tabsequence group by t1event, t2event, user, srp1, srp2 INTO OUTFILE 'tab_srp.csv'