SELECT hour::date as day,

avg (price) as ethprice

from ethereum.price.ez_prices_hourly

where token_address = lower ('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

and hour >='{{Start_Date}}' and hour <= '{{End_Date}}'

group by 1),

avaxpricet as (

SELECT hour::date as day,

avg (price) as avaxprice

from avalanche.price.ez_prices_hourly

where symbol ilike 'WAVAX'

and hour >='{{Start_Date}}' and hour <= '{{End_Date}}'

group by 1)

select 'Blast' as network,

count (Distinct tx_hash) as "Transactions",

count (distinct from_address) as "Total Users",

sum (tx_fee*ethprice) as "Total Fees [USD]",

avg (tx_fee*ethprice) as "Average Fees [USD]",

count (distinct block_number) as "Produced Blocks",

count (case when STATUS ilike 'SUCCESS' then 1 end) as "Successful Transactions",

count (case when STATUS ilike 'FAIL' then 1 end) as "Failed Transactions",

("Successful Transactions" / ("Successful Transactions" + "Failed Transactions")) * 100 as "Success Rate"

from blast.core.fact_transactions t1 join ethpricet t2 on t1.block_timestamp::date = t2.day

where block_timestamp >= '{{Start_Date}}' and block_timestamp <= '{{End_Date}}'

union all

select 'Avalanche' as network,

count (Distinct tx_hash) as "Transactions",

count (distinct from_address) as "Total Users",

sum (tx_fee*avaxprice) as "Total Fees [USD]",

avg (tx_fee*avaxprice) as "Average Fees [USD]",

count (distinct block_number) as "Produced Blocks",