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 "Total fee (in USD)",

sum(transaction_fee/1e24) as "Total fee (in NEAR)",

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

avg(transaction_fee/1e24) as "tx fee (in NEAR)",

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

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

group by 1

),

avg_info as (

select

case

when "Day" between '2022-01-01' and '2022-03-31' then 'Q1'

when "Day" between '2022-04-01' and '2022-06-30' then 'Q2'

when "Day" between '2022-07-01' and '2022-09-30' then 'Q3'

when "Day" between '2022-10-01' and '2022-12-31' then 'Q4'

else null

end as period,

avg("tx count") as "TX Count",

avg("Success Rate") as "Success Rate",

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

avg("Total fee (in USD)") as "Total fee (in USD)",

avg("Total fee (in NEAR)") as "Total fee (in NEAR)",