Untitled

WITH daily_p99_cross_region AS (
    SELECT
        date_trunc('day', date_parse(date, '%Y-%m-%d')) AS day,
        sdp,
        approx_percentile(bytes, 0.99) AS p99_value
    FROM
        your_table_name
    WHERE
        NOT regexp_like(sdp, '_\\w+_\\w+')  -- Exclude rows where letters after the second underscore are the same
    GROUP BY
        day, sdp
),
monthly_average_cross_region AS (
    SELECT
        date_format(day, '%Y-%m') AS year_month,
        sdp,
        AVG(p99_value) AS avg_p99_value
    FROM
        daily_p99_cross_region
    GROUP BY
        year_month, sdp
),
sdp_total_avg_cross_region AS (
    SELECT
        sdp,
        SUM(avg_p99_value) AS total_avg_p99
    FROM
        monthly_average_cross_region
    GROUP BY
        sdp
)
SELECT
    flowtype,
    COALESCE(SUM(total_avg_p99), 0) AS total_avg_p99_by_flowtype
FROM
    your_table_name t
LEFT JOIN
    sdp_total_avg_cross_region s ON t.sdp = s.sdp
GROUP BY
    flowtype;