select hour::date as date1,

avg (price) as usdprice

from ethereum.price.ez_hourly_token_prices

where symbol ilike 'weth' or symbol ilike 'eth'

group by 1),

solpricet as (

select recorded_hour::date as date1,

avg (close) as usdprice

from solana.price.ez_token_prices_hourly

where symbol ilike 'sol'

group by 1),

maintable as (

select 'Ethereum' as chain,

origin_from_address,

count (distinct t1.tx_hash) as Transactions,

sum (tx_fee*usdprice) as Fees

from ethereum.core.ez_decoded_event_logs t1 join ethpricet t2 on t1.block_timestamp::date = t2.date1

join ethereum.core.fact_transactions t3 on t1.tx_hash = t3.tx_hash

where contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'

and t1.block_timestamp >= '{{Start_Date}}'

and t1.tx_status = 'SUCCESS'

group by 1,2

union all

select 'Solana' as chain,

signers[0] as origin_from_address,

count (distinct tx_id) as Transactions,

sum (fee*usdprice)/1e9 as Fees

from solana.core.fact_transactions t1 join solpricet t2 on t1.block_timestamp::Date = t2.date1

where pre_token_balances[0]:mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'

and block_timestamp >= '{{Start_Date}}'

and succeeded ilike 'true'