rex-lin

WITH follower_counts AS
  (SELECT cast(date_created AS date) AS event_date,
          f.followed_user_id as user_id,
          SUM(CASE WHEN status = 'C' THEN 1 ELSE -1 END) AS follower_count
   FROM pf.dim_relationship_follow_by_following AS f
   WHERE f.followed_user_id IN (640739844686874809,
                                645108293177526966,
                                644968364267080539,
                                644915270711855294,
                                644972739110701600,

rex-lin

WITH 
 reflipped_user_id AS
  (SELECT DISTINCT created_by,
                   id AS flipagram_id
   FROM dim_flipagram
   WHERE id IN
       (SELECT DISTINCT flipagram_id AS reflipped_id
        FROM pf.dim_relationship_reflip_by_user AS f
        WHERE status = 'C'
          AND user_id IN (640739844686874809,

rex-lin

WITH follower_counts AS
  (SELECT f.followed_user_id ,
          SUM(CASE WHEN status = 'C' THEN 1 ELSE -1 END) AS COUNT
   FROM pf.dim_relationship_follow_by_following AS f
   GROUP BY 1),
     reflip_counts AS
  (SELECT user_id AS reflip_user_id,
          COUNT (DISTINCT flipagram_id) AS reflip_count
   FROM pf.dim_relationship_reflip_by_user AS f
   WHERE status = 'C'

rex-lin

------------------------------------------------- switched to UTC time (just in explore tab) --------------
------ FLIPS STARTED (all flips started with that hashtag whether from challenges or hashtag) --------
with flips_started as
(select *
from 
((select hc.day, 'Android' as OS, hc.hashtag, count(hc.hashtag) as flips_started, count(distinct e.user_id) as uniques_flips_started
from events147385 as e
join pf.dim_daily_challenge_hashtags as hc on (hc.day = e.event_time::date and hc.hashtag = json_extract_path_text(user_properties, 'Location ID') and json_extract_path_text(user_properties, 'Location') = 'Challenge Details' and json_extract_path_text(user_properties, 'Tab') = 'Explore' and hc.day between CURRENT_DATE - (30 *INTERVAL '1 day') AND CURRENT_DATE - (1*INTERVAL '1 day') )
where event_type = 'Flipagram Started' 
and e.event_time::date between CURRENT_DATE - (30 *INTERVAL '1 day') AND CURRENT_DATE - (1*INTERVAL '1 day')   

rex-lin

WITH flipagrams_with_tags AS
  (SELECT flipagram_id,
          a.token
   FROM pf.dim_flipagram_text_item a
   JOIN
     (SELECT token,
             COUNT (DISTINCT flipagram_id) AS flipagram_count
      FROM pf.dim_flipagram_text_item a
      JOIN pf.dim_flipagram b ON (a.flipagram_id=b.id
                                  AND b.date_created>= CURRENT_DATE - (30*INTERVAL '1 day'))

rex-lin

------------------------------------------------- switched to UTC time (just in explore tab) --------------


------ FLIPS STARTED (all flips started with that hashtag whether from challenges or hashtag) --------
with flips_started as
(select *
from 
((select hc.day, 'Android' as OS, hc.hashtag, count(hc.hashtag) as flips_started, count(distinct e.user_id) as uniques_flips_started
from events147385 as e
join pf.dim_daily_challenge_hashtags as hc on (hc.day = e.event_time::date and hc.hashtag = json_extract_path_text(user_properties, 'Location ID') and hc.day between CURRENT_DATE - (30 *INTERVAL '1 day') AND CURRENT_DATE)

rex-lin

with explore_visitors as
(select *
from
((select event_time::date as day, 'Android' as OS, count(1) as explore_visits, count(distinct e.user_id) as unique_explore_visitors
from events147385 as e
where event_type = 'Screen Shown' 
and json_extract_path_text(event_properties, 'Screen') = 'Explore'
and e.event_time::date between CURRENT_DATE - (30 *INTERVAL '1 day') AND CURRENT_DATE - (1*INTERVAL '1 day')
group by 1, 2)

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,
  '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