rex-lin

SELECT
  aquisition_date,
  source,
  campaign,
  locale,
  COUNT(DISTINCT firebase_id) AS users,
  SUM( session_start) AS sessions,
  SUM( Flipagram_Created) AS creations,
  SUM( Flipagram_View) AS views,
  SUM( Likes) AS likes,

rex-lin

SELECT
  source, locale,campaign, date(a.date) as date,
  COUNT(DISTINCT a.fullVisitorId) AS day0,
  COUNT(DISTINCT case when date(a.date) = date_add(date(b.date), INTERVAL -1 DAY) then b.fullVisitorId else null end) AS day1,
    COUNT(DISTINCT case when date(a.date) = date_add(date(b.date), INTERVAL -7 DAY) then b.fullVisitorId else null end) AS day7,
      COUNT(DISTINCT case when date(a.date) = date_add(date(b.date), INTERVAL -14 DAY) then b.fullVisitorId else null end) AS day14,
        COUNT(DISTINCT case when date(a.date) = date_add(date(b.date), INTERVAL -30 DAY) then b.fullVisitorId else null end) AS day30,
          COUNT(DISTINCT case when date(a.date) = date_add(date(b.date), INTERVAL -60 DAY) then b.fullVisitorId else null end) AS day60,
            COUNT(DISTINCT case when date(a.date) = date_add(date(b.date), INTERVAL -90 DAY) then b.fullVisitorId else null end) AS day90,
              COUNT(DISTINCT case when date(a.date) = date_add(date(b.date), INTERVAL -120 DAY) then b.fullVisitorId else nul

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 

rex-lin

SELECT
  install_date AS date,
  'Android' AS platform,
  users AS install_users,
  uninstall_users,
  avg_days_to_uninstall,
  avg_days_to_uninstall_15_day,
  zero_day_uninstalls
FROM (
  SELECT

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

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

------------------------------------------------- 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)