Top 50 browsers requesting /api/rest_v1/page/pdf (excluding bots and spiders) between 0:00 - 12:00 on 2017/09/26

ArchivedPublic

Actions

!set outputformat table;

set hive.mapred.mode=nonstrict;

with pdf_requests as (
  select
    user_agent_map["os_family"] as os_family,
    user_agent_map["browser_family"] as browser_family,
    count(*) as n
  from
    wmf.webrequest
  where
    year = 2017
    and month = 10
    and day = 11
    and hour = 1
    and webrequest_source = 'text'
    and uri_path like "%rest_v1/page/pdf%"
    and agent_type = "user"
  group by
    user_agent_map["os_family"],
    user_agent_map["browser_family"]
),
total_pdf_requests as (
  select
    sum(n) as total
  from
    pdf_requests
)

select
  pdf_requests_top_50.os_family as os_family,
  pdf_requests_top_50.browser_family as browser_family,
  pdf_requests_top_50.n as n,
  round(pdf_requests_top_50.n / total_pdf_requests.total * 100, 2) as pct
from
  total_pdf_requests
cross join (
  select
    *
  from
    pdf_requests
  order by
    n desc
  limit 50
) as pdf_requests_top_50
order by
  n desc

;