SELECT hour::date as day,

avg (price) as klayprice

from crosschain.price.ez_prices_hourly

where symbol ilike 'klay'

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 'Kaia' as network,

count (Distinct tx_hash) as "Transactions",

count (distinct from_address) as "Total Users",

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

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

count (distinct block_number) as "Produced Blocks",

count (case when TX_Succeeded ilike 'TRUE' then 1 end) as "Successful Transactions",

count (case when TX_Succeeded not ilike 'TRUE' then 1 end) as "Failed Transactions",

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

from kaia.core.fact_transactions t1 join klaypricet 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",