SELECT

destination_chain,

amount_usd,

ROW_NUMBER() OVER (PARTITION BY destination_chain ORDER BY amount_usd DESC) AS rn

FROM

arbitrum.defi.ez_bridge_activity

WHERE

block_timestamp <= CURRENT_DATE

AND destination_chain IN ('optimism', 'ethereum', 'avalanche')

AND amount_usd IS NOT NULL

),

aggregated_data AS (

SELECT

destination_chain,

COALESCE(SUM(CASE WHEN rn <= 1 THEN amount_usd ELSE 0 END), 0) AS top_1_volume,

COALESCE(SUM(CASE WHEN rn <= 10 THEN amount_usd ELSE 0 END), 0) AS top_10_volume,

COALESCE(SUM(CASE WHEN rn > 10 THEN amount_usd ELSE 0 END), 0) AS other_volume,

COALESCE(SUM(amount_usd), 0) AS total_volume

FROM

ranked_data

GROUP BY

destination_chain

ad.destination_chain,

TO_CHAR(ad.top_1_volume, 'FM9,999,999,999,999,999,999,999,999,999,999') AS top_1_volume,

TO_CHAR(ad.top_10_volume, 'FM9,999,999,999,999,999,999,999,999,999,999') AS top_10_volume,

TO_CHAR(ad.other_volume, 'FM9,999,999,999,999,999,999,999,999,999,999') AS other_volume,

TO_CHAR(ad.total_volume, 'FM9,999,999,999,999,999,999,999,999,999,999') AS total_volume,

CASE WHEN ad.total_volume > 0 THEN ROUND((ad.top_1_volume / ad.total_volume) * 100, 2) || '%' ELSE '0.00%' END AS top_1_percentage,

CASE WHEN ad.total_volume > 0 THEN ROUND(((ad.top_10_volume - ad.top_1_volume) / ad.total_volume) * 100, 2) || '%' ELSE '0.00%' END AS top_10_percentage,

CASE WHEN ad.total_volume > 0 THEN ROUND((ad.other_volume / ad.total_volume) * 100, 2) || '%' ELSE '0.00%' END AS other_percentage

aggregated_data ad

ORDER BY