Untitled

WITH hourly_cross_region_traffic AS (
    SELECT
        date_trunc('hour', date_parse(date, '%Y-%m-%d %H:%i:%s')) AS hour,
        flowtype,
        bucket,
        SUM(bytes) AS total_bytes
    FROM
        s3backbonedatabase-prod
    WHERE
        NOT regexp_like(sdp, '_\\w+_\\w+')  
    GROUP BY
        hour, flowtype, bucket
),
daily_max_traffic AS (
    SELECT
        date_trunc('day', hour) AS day,
        flowtype,
        bucket,
        MAX(total_bytes) AS max_bytes
    FROM
        hourly_cross_region_traffic
    GROUP BY
        day, flowtype, bucket
),
monthly_avg_traffic AS (
    SELECT
        date_format(day, '%Y-%m') AS year_month,
        flowtype,
        bucket,
        AVG(max_bytes) AS avg_max_bytes
    FROM
        daily_max_traffic
    GROUP BY
        year_month, flowtype, bucket
)
SELECT
    flowtype,
    bucket,
    COALESCE(SUM(avg_max_bytes), 0) AS total_avg_max_bytes
FROM
    monthly_avg_traffic
GROUP BY
    flowtype, bucket
ORDER BY
    total_avg_max_bytes DESC
LIMIT 50;