SELECT

date_trunc('day', RECORDED_HOUR) as pdate,

avg(price) as Price

FROM osmosis.price.ez_prices

WHERE symbol = 'SEI'

GROUP BY 1 )

select

split(date_trunc('day', block_timestamp),' ')[0] as date,

count(distinct TX_ID) as txs,

txs/count(DISTINCT Block_timestamp::Date)/86400 as avg_tps,

avg((SUBSTR(FEE, 1, len(FEE) -4)::Int / 1e6)*Price) as Fee,

count(DISTINCT TX_FROM) as users,

(sum(case when TX_SUCCEEDED = 'TRUE' then 1 else 0 end)/txs)*100 as succeeded_txs,

txs/users as tx_per_user,

txs/Count(DISTINCT BLOCK_TIMESTAMP::DATE) as tx_per_day,

users/count(DISTINCT BLOCK_TIMESTAMP::Date) as user_per_day

from sei.core.fact_transactions

join seiprice on BLOCK_TIMESTAMP::DATE=pdate

where BLOCK_TIMESTAMP::DATE < current_date

group by 1

order by 1 asc