rex-lin

select created_by as user_id, b.username as username, b.name as name, count(1) as feature_count, max(a.date_created) as last_feature
from pf.dim_flipagram a
join pf.dim_user b on a.created_by = b.id
where a.date_created >= '2017-01-01'
and featured = 'True'
group by 1,2,3
order by 4 desc
limit 1000

rex-lin

-- adjuststed for daylights savings

drop table analysts.push_sent_appboy_ios;
drop table analysts.push_open_appboy_ios;
drop table analysts.push_open_amplitude_ios;

SELECT external_user_id, (time + (0*INTERVAL '1 hour'))::date as date, min((time + (0*INTERVAL '1 hour'))) as event_time
into analysts.push_sent_appboy_ios
FROM appboy."event"
where (time + (0*INTERVAL '1 hour'))::date between '2017-03-15' and '2017-04-25'

rex-lin

-- Install and Referred Sessions Events
with user_mapping as (
SELECT branch_identity_id, developer_identity as user_id
FROM branchio.event a
where developer_identity is not null
and developer_identity != ''
group by 1,2
)

select event_date, device_os as os, event_name, session_referring_link_channel as channel, session_referring_link_feature as feature, session_referring_link_campaign as campaign, session_referring_link_tags as tags, branch_identity_id

rex-lin

select date, sum(elapsed_time) as duration, count(distinct client_id) as user_count
from (
select event_timestamp::date as date, EXTRACT(SECOND FROM session_stop_timestamp-session_start_timestamp) as elapsed_time, client_id
from awsma.v_event 
where event_timestamp between '2017-03-10' and '2017-03-13'
and event_type = '_session.stop'
and application_sdk_name = 'aws-sdk-iOS'
)
where elapsed_time <= 7200

rex-lin

select date_created, moderator, sum(case when action_taken = 'FLIPAGRAM_MODERATION_APPROVED' then 1 else 0 end) as approved_count,
sum(case when action_taken = 'FLIPAGRAM_MODERATION_NOT_APPROVED' then 1 else 0 end) as not_approved_count
from pa.vw_interim_moderator_action 
group by 1,2
order by 1,2 asc

rex-lin

- Big Query ---
-- export csv of this data and import into redshift

select user_id, source, campaign, date
from (
select user_id, source, campaign,date, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY date ASC) row_num
from (

SELECT
  ud.value.value.string_value AS user_id,

rex-lin

-- adjuststed for daylights savings

drop table analysts.push_sent_appboy;
drop table analysts.push_open_appboy;
drop table analysts.push_open_amplitude;

SELECT external_user_id, (time + (0*INTERVAL '1 hour'))::date as date, min((time + (0*INTERVAL '1 hour'))) as event_time
into analysts.push_sent_appboy
FROM appboy."event"
where (time + (0*INTERVAL '1 hour'))::date between '2017-03-15' and '2017-04-25'

rex-lin

----------historical flip check with engagement--------------
with flipagram_check as (
select a.flipagram_id
from (
SELECT id as flipagram_id, created_by as user_id
FROM pf.dim_flipagram
where date_created between '2013-01-01' and '2016-12-31'
and duration between 3000 and 30000
and status = 'PUBLIC') a
join 

rex-lin

----------SUL flip check--------------
drop table analysts.sul_flips_filtered;

with flipagram_check as (
SELECT id as flipagram_id
FROM pf.dim_flipagram 
where date_created between '2013-01-01' and '2017-12-31'
and duration between 3000 and 30000
and status = 'PUBLIC'
),

rex-lin

----------recent flip check--------------
with flipagram_check as (
select a.flipagram_id, music_title, music_artist
from (
SELECT id as flipagram_id, created_by as user_id, coalesced_music_track_title as music_title, coalesced_music_artist_name as music_artist
FROM pf.dim_flipagram
where date_created between '2016-11-01' and '2017-02-07'
and duration between 3000 and 60000
and status = 'PUBLIC') a
join