Get most popular URLs by request share

[trieloff]

SELECT * 
FROM (
  SELECT reqs, 1 - (SUM(reqs) OVER(ORDER BY reqs)/total) AS rshare, req_http_X_URL, 
  FROM (
    SELECT 
      COUNT(time_start_usec) AS reqs, 
      req_http_X_URL, 
      sum(count(*)) OVER() AS total,
    FROM `helix-225321.helix_logging_….requests202010`   
    WHERE status_code IN ("200") AND
    time_start_usec > CAST(UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 2 DAY)) AS STRING) AND
    time_start_usec < CAST(UNIX_MICROS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)) AS STRING)
    GROUP BY req_http_X_URL 
    ORDER BY reqs DESC)
  ORDER BY reqs DESC)
WHERE rshare < 0.5 # AND
# req_http_X_URL LIKE "/en/publish%"