date_trunc('day', block_timestamp) as day,

sum(event_inputs:value/pow(10,18)) * avg(price2) as volume,

(sum(event_inputs:value/pow(10,18))/count(distinct tx_hash)) * avg(price2) as average ,

count(distinct event_inputs:from) as swappers,

COUNT(DISTINCT tx_hash) as swaps

from avalanche.core.fact_event_logs

LEFT outer JOIN (

SELECT

date_trunc('day',RECORDED_HOUR) as day2,

avg(open) as price2

FROM crosschain.core.fact_hourly_prices

where id LIKE 'avalanche-2'

GROUP BY 1

) ON date_trunc('day', block_timestamp) = day2

where contract_address = lower('0xB31f66AA3C1e785363F0875A1B74E27b85FD66c7')

and event_name = 'Transfer'

and tx_hash in (

select tx_hash

from avalanche.core.fact_event_logs

where event_name = 'Swap' and block_timestamp::date >= '2023-01-22'

) --

AND (event_inputs['to'] LIKE ORIGIN_FROM_ADDRESS OR event_inputs['from'] LIKE ORIGIN_FROM_ADDRESS)

GROUP BY 1