sess.sql

with
installed_users as (
  select distinct
         os
       , gps_adid
       , idfa
       , idfv
    from install_log
    -- ログはUTC時間で収集されているので、JSTに合わせている
    -- dt は hour 単位
   where date(date_parse(dt, '%Y%m%d%H') AT TIME ZONE 'Asia/Tokyo') = date('${install_jst_date}')
),
active_users as (
    select gps_adid
         , idfa
         , idfv
         , session_id
         , min(dt) as dt
      from client_log
     where date(date_parse(dt, '%Y%m%d%H') AT TIME ZONE 'Asia/Tokyo') = date('${launch_jst_date}')
       and event_type = 'Launch'  -- 起動したというイベント
  group by 1, 2, 3, 4
  order by dt
)
  select distinct
         nullif(coalesce(installed_users.gps_adid, active_users.gps_adid), '-') as gps_adid
       , nullif(coalesce(installed_users.idfa, active_users.idfa), '-') as idfa
       , nullif(coalesce(installed_users.idfv, active_users.idfv), '-') as idfv
       , to_unixtime(date_parse(array_agg(active_users.dt)[${num_sessions}], '%Y%m%d%H')) as created_at_unix
       , to_unixtime(date_parse(array_agg(active_users.dt)[${num_sessions}], '%Y%m%d%H') + interval '28' day) as time_to_exist
       , os
    from installed_users
    join active_users
      on installed_users.gps_adid = active_users.gps_adid
      or installed_users.idfa = active_users.idfa
      or installed_users.idfv = active_users.idfv
group by 1, 2, 3, 6
  having count(session_id) >= ${num_sessions}