'Blitz' as platform,

tx_hash,

trader,

symbol,

case when is_taker = 'true' then amount_usd else 0 end as amount

from blast.blitz.ez_perp_trades

UNION all

select block_timestamp,

'Vertex' as platform,

tx_hash,

trader,

symbol,

case when is_taker = 'true' then amount_usd else 0 end as amount

from arbitrum.vertex.ez_perp_trades

UNION all

select block_timestamp,

'Vertex' as platform,

tx_hash,

trader,

symbol,

case when is_taker = 'true' then amount_usd else 0 end as amount

from arbitrum.vertex.ez_spot_trades

select trunc(block_timestamp,'day') as date,

count(DISTINCT trader) as traders,

count(DISTINCT tx_hash) as tx,

sum(amount) as volume,

avg(amount) as avg_volume,

median(amount) as medain_volume,

max(amount) as max_volume,

avg(volume) over (order by date rows between 30 preceding and current row) as avg_30_day_moving,

avg(volume) over (order by date rows between 14 preceding and current row) as avg_14_day_moving,

avg(volume) over (order by date rows between 7 preceding and current row) as avg_7_day_moving

from blitz