Queries memo

[ts-3156]

-- landing page uu per channel
select
  gd_day(created_at, 'UTC') day,
  channel,
  count(distinct session_id)
from
  search_logs
where
  device_type not in ('crawler', 'UNKNOWN')
  and where landing = true
  and created_at between {start_time} and {end_time}
group by
  day,
  channel

-- search rate per channel
select
  gd_day(created_at, 'UTC') day,
  channel,
  count(distinct if(action = 'show', session_id, NULL)) / count(distinct session_id) rate
from
  search_logs
where
  device_type not in ('crawler', 'UNKNOWN')
  and created_at between {start_time} and {end_time}
group by
  day,
  channel

-- sign_in rate per channel
select
  gd_day(created_at, 'UTC') day,
  channel,
  count(distinct if(user_id != -1, session_id, NULL)) / count(distinct session_id) rate
from
  search_logs
where
  device_type not in ('crawler', 'UNKNOWN')
  and created_at between {start_time} and {end_time}
group by
  day,
  channel