queries

SELECT api_method, country_code, guid, event_timestamp INTO __tmp_apis_feb2 FROM apis WHERE event_day = '2015-02-01' AND guid <> '-' AND api_method IN ('i:view.external.scrobbler.showed.com.google.android.music','i:view.external.scrobbler.showed.com.spotify.mobile.android','i:view.external.scrobbler.showed.com.sonyericsson.music','i:view.external.scrobbler.showed.com.lge.music','i:view.external.scrobbler.showed.com.maxmpz.audioplayer','i:view.external.scrobbler.showed.com.htc.music','i:view.external.scrobbler.showed.deezer.android.app','i:view.external.scrobbler.showed.com.tbig.playerprotrial','i:view.external.scrobbler.showed.com.miui.player','i:view.external.scrobbler.showed.com.jrtstudio.music','i:view.external.scrobbler.showed.fm.last.android','i:view.external.scrobbler.showed.com.amazon.mp3','i:view.external.scrobbler.showed.com.rdio.android') ;

SELECT t.api_method AS TYPE,t.guid AS guid,t.country_code AS country, t.event_timestamp AS event_timestamp, v.abstrack AS abstrack,ag.genre AS genre, MIN (EXTRACT(EPOCH FROM(t.event_timestamp - v.event_timestamp))) AS lag  INTO __tmp_apis_abstrack_feb2 FROM __tmp_apis_feb2 AS t JOIN views AS v ON (t.guid = v.guid AND EXTRACT(EPOCH FROM(t.event_timestamp - v.event_timestamp)) <= 40 AND EXTRACT(EPOCH FROM(t.event_timestamp - v.event_timestamp)) >=0) JOIN __tmp_abstrack_unique_genre ag ON v.abstrack = ag.abstrack WHERE v.event_day = '2015-02-01'  AND v.guid <> '-' GROUP BY t.api_method, t.guid, t.country_code, t.event_timestamp, v.abstrack, ag.genre;