select
-- 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