Untitled

WITH first_open as(
    SELECT DISTINCT
    user_pseudo_id,
    DATE(timestamp_millis((SELECT value.int_value FROM UNNEST(user_properties) WHERE key='first_open_time'))) AS first_date_open,
  FROM 
    `wellbeing-a817f.analytics_260849188.events_*` 
  where 
    (platform="IOS") and (stream_id='2603039547')),

sessions as(
  SELECT DISTINCT
      user_pseudo_id,
      FORMAT_DATE('%Y-%m-%d', PARSE_DATE('%Y%m%d', event_date)) as action_days
      FROM 
      `wellbeing-a817f.analytics_260849188.events_*` 
      WHERE 
      (platform="IOS") and (stream_id='2603039547')
      group by user_pseudo_id, event_date
      order by user_pseudo_id, action_days desc)


SELECT
  *,
  COALESCE(date_diff(action_days,lag_day, day), 0) as inaction_days,
  case 
    when first_date_open = action_days then 'New User'
    WHEN DATE_DIFF(DATE(action_days), DATE(lag_day), day) >= 14 then 'inactive_user' end
FROM
  (SELECT
    sessions.user_pseudo_id,
    first_date_open,
    DATE(action_days) as action_days,
    DATE(LAG(action_days,1) OVER (Partition by sessions.user_pseudo_id ORDER BY action_days)) as lag_day,
  FROM first_open
  LEFT JOIN sessions on sessions.user_pseudo_id=first_open.user_pseudo_id
  ORDER BY
    sessions.user_pseudo_id, action_days, lag_day desc)