symbol_in || '➡' || symbol_out as "Token Pair", case

when token_in = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then sum(amount_in)

when token_out = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then sum(amount_out)

end as volume, sum(coalesce(amount_in_usd,amount_out_usd)) as volume_usd, platform as "DEX", case

when token_in = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then 'Sell'

when token_out = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389') then 'Buy'

end as swap_direction, 'Ethereum' as "Chain"

from ethereum.defi.ez_dex_swaps

where (token_in = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389')

or token_out = lower('0xAdF7C35560035944e805D98fF17d58CDe2449389'))

and block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'

group by block_timestamp, token_in, token_out, tx_hash, origin_from_address, symbol_in, symbol_out, platform

union all

select block_timestamp, tx_hash, origin_from_address as swapper,

symbol_in || '➡' || symbol_out as "Token Pair", case

when token_in = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then sum(amount_in)

when token_out = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then sum(amount_out)

end as volume, sum(coalesce(amount_in_usd,amount_out_usd)) as volume_usd, platform as "DEX", case

when token_in = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then 'Sell'

when token_out = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431') then 'Buy'

end as swap_direction, 'Base' as "Chain"

from base.defi.ez_dex_swaps

where (token_in = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431')

or token_out = lower('0x96419929d7949D6A801A6909c145C8EEf6A40431'))

and block_timestamp::date between '{{Start_Date}}' and '{{End_Date}}'

group by block_timestamp, token_in, token_out, tx_hash, origin_from_address, symbol_in, symbol_out, platform)

select "DEX", count(distinct tx_hash) as "Swap Count", ROUND(sum(volume),2) as "Swap Volume ($SPEC)",

ROUND(sum(volume_usd),2) as "Swap Volume ($USD)", count(distinct swapper) as "Swapper Count"

from overview

group by 1