select

date_trunc('day', recorded_hour) as date,

(avg(open) + avg(close)) / 2 as price

from crosschain.core.fact_hourly_prices

where id = 'terra-luna-2'

group by 1

),

info_tx as (

select

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

count(distinct tx_id) as "tx count",

-- count(distinct tx_from) as "Active users",

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

from terra.core.fact_transactions

where "Day" >= current_date - 90

group by 1

),

info_fee as (

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

sum(fee * price) as "fee",

avg(fee * price) as "Avg tx fee"

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

where

tx_succeeded = TRUE and

fee_denom = 'uluna' and

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