A as( select 'ETH' as tag, '⚫️ Ethereum' as chain, block_timestamp, amount_out_usd, origin_from_address from ethereum.defi.ez_dex_swaps

union all select 'L2s' as tag, '🔵 Arbitrum' as chain, block_timestamp, amount_out_usd, origin_from_address from arbitrum.defi.ez_dex_swaps

union all select 'L2s' as tag, '🔴 Optimism' as chain, block_timestamp, amount_out_usd, origin_from_address from optimism.defi.ez_dex_swaps

union all select 'L2s' as tag, '🔵 Base ' as chain, block_timestamp, amount_out_usd, origin_from_address from base.defi.ez_dex_swaps

union all select 'L1s' as tag, '🟣 Polygon' as chain, block_timestamp, amount_out_usd, origin_from_address from polygon.defi.ez_dex_swaps

union all select 'L1s' as tag, '🔴 Avalanche' as chain, block_timestamp, amount_out_usd, origin_from_address from avalanche.defi.ez_dex_swaps

union all select 'L1s' as tag, '🟡 BNB Chain' as chain, block_timestamp, amount_out_usd, origin_from_address from bsc.defi.ez_dex_swaps

),

B as(

select tag, chain, sum(amount_out_usd) as volume

from A

where block_timestamp >= current_date - interval '720 hours'

group by 1,2),

C as(

select tag, chain, sum(amount_out_usd) as volumes

from A

where block_timestamp <= current_date - interval '720 hours'

and block_timestamp >= current_date - interval '1440 hours'

group by 1,2)

select

a.chain as "Blockchain ecosystem",

to_varchar(volume,'$999,999,999,999,999') as "Swap volume (30 days)",

case when volume > volumes

then '➕ '||round((volume/volumes-1)*100)

else '➖ '||round((volumes/volume-1)*100)

end || '%' as "% change (30 days)",

a.tag as "EVM TYPE"

from B as a

left join C as b on a.chain = b.chain

order by volume desc