select

date_trunc('day', timestamp) as date,

avg(price_usd) as near_usd

from near.core.fact_prices

where

symbol = 'wNEAR'

group by 1

),

info as (

select

date_trunc('day', block_timestamp) as "Day",

count(distinct tx_hash) as "tx count",

count(distinct tx_signer) as "Active users",

sum((transaction_fee/1e24) * near_usd) as "fee",

avg((transaction_fee/1e24) * near_usd) as "Avg tx fee",

(sum(iff(tx_status = 'Success', 1, 0)) / "tx count") * 100 as "Success Rate",

avg("tx count") over (order by "Day", "Day" rows between 6 preceding and current row) as "MA7 tx count",

avg("fee") over (order by "Day", "Day" rows between 6 preceding and current row) as "MA7 fee",

sum("tx count") over (order by "Day") as "Cum tx count",

sum("fee") over (order by "Day") as "Cum fee"

from near.core.fact_transactions join price on block_timestamp::date = date

where "Day" >= current_date - 90

group by 1

),

avg_info as (

select

avg("tx count") as "AVG tx count",

avg("Success Rate") as "AVG success rate",

avg("Active users") as "AVG Active users",

avg("fee") as "AVG fee",

avg("Avg tx fee") as "AVG tx fee per day"

from info