distinct tx_hash

from avalanche.core.fact_event_logs

where event_name ilike 'swap'

select

'swap' as type,

date_trunc('hour',block_timestamp) as "date(hour)",

date_trunc('day',block_timestamp) as "date(day)",

count(distinct tx_hash) as "count of action",

sum("count of action") over (order by "date(hour)") as "cumulative count of action",

count(distinct origin_from_address) "count of users",

sum(event_inputs:value/power(10,6)) as "total action volume",

sum("total action volume") over (order by "date(hour)") as "cumulative action volume",

avg(event_inputs:value/power(10,6)) as "average action volume"

from avalanche.core.fact_event_logs

where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and event_inputs:value/1e6 < 1e9

and tx_status = 'SUCCESS'

and block_timestamp >= '2022-07-01'

and tx_hash in (select tx_hash from table1)

group by 1,2,3

union

select

'transfer' as type,

date_trunc('hour',block_timestamp) as "date(hour)",

date_trunc('day',block_timestamp) as "date(day)",

count(distinct tx_hash) as "count of action",

sum("count of action") over (order by "date(hour)") as "cumulative count of action",

count(distinct origin_from_address) "count of users",

sum(event_inputs:value/power(10,6)) as "total action volume",

sum("total action volume") over (order by "date(hour)") as "cumulative action volume",

avg(event_inputs:value/power(10,6)) as "average action volume"

from avalanche.core.fact_event_logs

where contract_address = '0xb97ef9ef8734c71904d8002f8b6bc66dd9c48a6e'

and event_inputs:value/1e6 < 1e9