Untitled
unknown
plain_text
3 years ago
1.3 kB
5
Indexable
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)
Editor is loading...