Untitled
unknown
plain_text
a year ago
907 B
6
Indexable
WITH hourly_cross_region_traffic AS ( SELECT date_trunc('hour', date_parse(date, '%Y-%m-%d %H:%i:%s')) AS hour, bucket, SUM(bytes) AS total_bytes FROM s3backbonedatabase-prod WHERE NOT regexp_like(sdp, '_\\w+_\\w+') GROUP BY hour, bucket ), daily_max_traffic AS ( SELECT date_trunc('day', hour) AS day, bucket, MAX(total_bytes) AS max_bytes FROM hourly_cross_region_traffic GROUP BY day, bucket ), monthly_avg_traffic AS ( SELECT date_format(day, '%Y-%m') AS year_month, bucket, AVG(max_bytes) AS avg_max_bytes FROM daily_max_traffic GROUP BY year_month, bucket ) SELECT bucket, COALESCE(SUM(avg_max_bytes), 0) AS total_avg_max_bytes FROM monthly_avg_traffic GROUP BY bucket ORDER BY total_avg_max_bytes DESC LIMIT 50;
Editor is loading...
Leave a Comment