select

origin_from_address,

tx_hash,

block_timestamp,

utils.udf_hex_to_int(SUBSTR(data, 67, 64))/POW(10,6) as token_amount,

contract_address

from flow.core_evm.fact_event_logs

where contract_address in ('0x20ca5d1c8623ba6ac8f02e41ccaffe7bb6c92b57','0x073d6f03d1f1724f9daa11b7f61a45105607f88c')

and topic_0='0x8b3e96f2b889fa771c53c981b40daf005f63f637f1869f707052d15a3dd97140'

and tx_succeeded='TRUE'

and origin_function_signature='0x3df02124'

select trunc (block_timestamp,'day') as date, case when contract_address='0x073d6f03d1f1724f9daa11b7f61a45105607f88c' then 'StableCeler'

else 'StableGate' end as pool,

count(distinct tx_hash) as swaps,

sum(swaps) over (partition by pool order by date) as total_swaps,

sum(token_amount) as volume_swapped,

sum(volume_swapped) over (partition by pool order by date) as total_volume_swapped,

avg(token_amount) as avg_volume_swapped,

count(distinct origin_from_address) as swappers

from info

group by 1,2 order by 1 desc, 2