SELECT

trader,

SUM(amount_usd) AS total_volume

FROM (

SELECT trader, amount_usd FROM arbitrum.vertex.ez_perp_trades

UNION ALL

SELECT trader, amount_usd FROM arbitrum.vertex.ez_spot_trades

) combined_trades

GROUP BY trader

),

total_volume AS (

SELECT SUM(total_volume) AS overall_volume FROM user_volume

),

power_users AS (

SELECT

trader,

total_volume,

NTILE(10) OVER (ORDER BY total_volume DESC) AS decile

FROM user_volume

SELECT

decile,

COUNT( trader) AS num_users,

SUM(total_volume) AS volume_by_decile,

ROUND(SUM(total_volume) * 100.0 / (SELECT overall_volume FROM total_volume), 2) AS percentage_contribution

FROM power_users

GROUP BY decile;