with raw_data as (

select

t.tx_hash,

t.from_address,

e.contract_address

from avalanche.core.fact_transactions t

inner join avalanche.core.fact_decoded_event_logs e on t.tx_hash=e.tx_hash

where (t.block_timestamp>='{{FromDate}}' and t.block_timestamp<='{{ToDate}}') and t.STATUS='SUCCESS'

and (case when '{{Contracts}}'='All' then True

else e.contract_address=lower('{{Contract}}') end)

and (case when '{{Users}}'='All' then True

else t.from_address=lower('{{User}}') end)

--contract 0xE3140141b636F28d1e5f4005b48b14f549b619eC

--user 0xCd19A5475983D85bdb77991FA886105743a9CbE9

),

transactions_activity as (

select

count(distinct tx_hash) n_tx,

count(distinct from_address) active_users,

count(distinct contract_address) contracts

from raw_data

),

tx_fees as (

select

sum(tx_fee) fees,

avg(tx_fee) avg_fee,

max(tx_fee) max_fee,

min(tx_fee) min_fee,

median(tx_fee) med_fee

from avalanche.core.fact_transactions t

where (block_timestamp>='{{FromDate}}' and block_timestamp<='{{ToDate}}') and STATUS='SUCCESS'

and (case when '{{Users}}'='All' then True