SELECT hour::date as day,

avg (price) as flowprice

from flow.price.ez_prices_hourly

where symbol ilike 'FLOW'

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

count (Distinct t1.tx_id) as "Transactions",

count (distinct authorizers) as "Total Users",

sum (event_data:amount*flowprice) as "Total Fees [USD]",

avg (event_data:amount*flowprice) as "Average Fees [USD]",

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

count (case when t1.TX_Succeeded ilike 'FALSE' then 1 end) as "Failed Transactions",

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

from flow.core.fact_transactions t1 join flowpricet t2 on t1.block_timestamp::date = t2.day

join flow.core.fact_events t3 on t1.tx_id = t3.tx_id

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

and t3.event_type = 'FeesDeducted'

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]",