rex-lin

with view_durations as (

select client_id, a_flipagram_id as flipagram_id, m_played_duration as play_duration
from awsma.v_event
where event_timestamp between '2017-01-01' and '2017-01-15'
and event_type = 'ping'
),

flipagrams as (

rex-lin

select event_timestamp::date as date, count(distinct client_id) as users, sum(m_played_duration) as play_duration
from awsma.v_event
where event_timestamp between '2016-11-01' and '2017-02-15'
and event_type = 'ping'
and a_location ='Popular Flipagram Feed'

group by 1
order by 1 asc

rex-lin

with flipagrams as (

select id as flipagram_id, has_music
from pf.dim_flipagram
where date_created between '2017-01-01' and '2017-01-15'
and duration >= 3000
),


interactions as (

rex-lin

----------recent flip check--------------
-- 480x480 min resolution
-- all video
-- square and portrait aspect ratios
-- public flips
-- public users
-- duration between 3s to 30s

with flipagram_check as (
select a.flipagram_id

rex-lin

SELECT
  a.date,
  'Android' AS platform,
  COUNT(DISTINCT a.fullVisitorId) AS day0,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -1 DAY) THEN b.fullVisitorId ELSE NULL END) AS day1,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -7 DAY) THEN b.fullVisitorId ELSE NULL END) AS day7,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -14 DAY) THEN b.fullVisitorId ELSE NULL END) AS day14,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -30 DAY) THEN b.fullVisitorId ELSE NULL END) AS day30,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -60 DAY) THEN b.fullVisitorId ELSE NULL END) AS day60,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -90 DAY) THEN b.fullVisitorId ELSE NULL END) AS day90

rex-lin

SELECT
  a.date,
  'iOS' AS platform,
  COUNT(DISTINCT a.fullVisitorId) AS day0,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -1 DAY) THEN b.fullVisitorId ELSE NULL END) AS day1,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -7 DAY) THEN b.fullVisitorId ELSE NULL END) AS day7,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -14 DAY) THEN b.fullVisitorId ELSE NULL END) AS day14,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -30 DAY) THEN b.fullVisitorId ELSE NULL END) AS day30,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -60 DAY) THEN b.fullVisitorId ELSE NULL END) AS day60,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -90 DAY) THEN b.fullVisitorId ELSE NULL END) AS day90

rex-lin

SELECT
  a.date,
  'iOS' AS platform,
    COUNT(DISTINCT a.fullVisitorId) AS day0, 
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -1 DAY) THEN b.fullVisitorId ELSE NULL END) AS day1,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -7 DAY) THEN b.fullVisitorId ELSE NULL END) AS day7,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -14 DAY) THEN b.fullVisitorId ELSE NULL END) AS day14,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -30 DAY) THEN b.fullVisitorId ELSE NULL END) AS day30,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -40 DAY) THEN b.fullVisitorId ELSE NULL END) AS day40,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -50 DAY) THEN b.fullVisitorId ELSE NULL END) AS day50,

rex-lin

SELECT
  a.date,
  'Android' AS platform,
  COUNT(DISTINCT a.fullVisitorId) AS day0, 
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -1 DAY) THEN b.fullVisitorId ELSE NULL END) AS day1,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -7 DAY) THEN b.fullVisitorId ELSE NULL END) AS day7,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -14 DAY) THEN b.fullVisitorId ELSE NULL END) AS day14,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -30 DAY) THEN b.fullVisitorId ELSE NULL END) AS day30,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -40 DAY) THEN b.fullVisitorId ELSE NULL END) AS day40,
  COUNT(DISTINCT CASE WHEN a.date <= date_add(b.date, INTERVAL -50 DAY) THEN b.fullVisitorId ELSE NULL END) AS day50,

rex-lin

drop table analysts.ios_ad_retention;

SELECT

  a.date,
  'iOS' AS platform, a.country, a.language, a.keyword, a.campaign,
  COUNT(DISTINCT a.fullVisitorId) AS day0, 
  COUNT(DISTINCT CASE WHEN a.date = b.date - (1 * INTERVAL '1 DAY') THEN b.fullVisitorId ELSE NULL END) AS day1,
  COUNT(DISTINCT CASE WHEN a.date = b.date - (7 * INTERVAL '1 DAY')  THEN b.fullVisitorId ELSE NULL END) AS day7,
  COUNT(DISTINCT CASE WHEN a.date = b.date - (14 * INTERVAL '1 DAY')  THEN b.fullVisitorId ELSE NULL END) AS day14,

rex-lin

drop table analysts.retention_rex;

with users as (
select id as user_id, date(date_created) as date_created, locale, has_avatar
from pf.dim_user
where date_created >= '2016-10-01'

),

push_detailed as (