Untitled

WITH daily_p99_cross_region AS (
    SELECT
        date,
        sdp,
        PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY bytes) OVER (PARTITION BY date, sdp) AS p99_value
    FROM
        your_table_name
    WHERE
        sdp NOT LIKE '%\_%'  -- Filter for in-region traffic based on the pattern
),
monthly_average_cross_region AS (
    SELECT
        SUBSTRING(date, 1, 6) 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;