-- traffic per host per hour on 2022-01-27
 select split(hostname, '.')[1] as host,
        split(hostname, '.')[2] as dc,
        webrequest_source,
        count_actual,
        day,
        hour
   from wmf_raw.webrequest_sequence_stats
  where year = 2022
    and month = 1
    and day = 27

-- total traffic per host for the whole day
 select split(hostname, '.')[1] as host,
        split(hostname, '.')[2] as dc,
        webrequest_source,
        sum(count_actual) as total_count
   from wmf_raw.webrequest_sequence_stats
  where year = 2022
    and month = 1
    and day = 27
  group by hostname, webrequest_source


-- average traffic per host on a given dc and source
with total_traffic_by_host as (
 select year, month, day,
        split(hostname, '.')[1] as host,
        split(hostname, '.')[2] as dc,
        webrequest_source,
        sum(count_actual) as total_count
   from wmf_raw.webrequest_sequence_stats
  where split(hostname, '.')[2] in ('ulsfo', 'eqiad')
    and ((year=2022) or (year=2021 and month > 9))
  group by year, month, day, hostname, webrequest_source
)
 select year, month, day,
        dc,
        webrequest_source,
        sum(total_count) / count(distinct host) as avg_traffic_per_host,
        sum(total_count) as total_traffic,
        count(distinct host) as hosts_responding
   from total_traffic_by_host
  group by year, month, day, dc, webrequest_source
  order by year, month, day, dc, webrequest_source


-- total requests per source per day
 select year, month, day,
        webrequest_source,
        sum(count_actual) as total_count
   from wmf_raw.webrequest_sequence_stats
  where ((year=2022) or (year=2021 and month > 9))
  group by year, month, day, webrequest_source


-- try and find variability by access_method or referer_class
 select split(hostname, '.')[1] as host,
        split(hostname, '.')[2] as dc,
        access_method,
        count(1) as pageviews
   from wmf.pageview_actor
  where year = 2022
    and month = 1
    and day = 27
    and is_pageview
  group by hostname, access_method