ELB_percentile_query.sql

WITH total_count_calc AS
(
  SELECT COUNT(*) AS total_count
  FROM elb_logs_raw_partition
  WHERE YEAR = '2016'
  AND   MONTH = '12'
  AND   DAY = '15'
),
status_count_calc AS
(
  SELECT elb_response_code,
         COUNT(*) AS status_count
  FROM elb_logs_raw_partition
  WHERE YEAR = '2016'
  AND   MONTH = '12'
  AND   DAY = '15'
  GROUP BY elb_response_code
)
SELECT SUM(percentile) as success_percentile
FROM (SELECT elb_response_code,
             status_count,
             total_count,
             status_count / CAST(total_count AS DOUBLE)*100 AS percentile
      FROM status_count_calc
        INNER JOIN total_count_calc ON 1 = 1)
WHERE elb_response_code IN ('200','204');