with price as (

select

date_trunc('day', recorded_at) as date,

avg(price) as osmo_usd

from osmosis.core.dim_prices

where

symbol = 'OSMO'

group by 1

),

change_tx as (

select

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

count(distinct tx_id) as "24h Transactions",

lag("24h Transactions",1) over(order by "Day") as "Previous 24h Transactions",

(("24h Transactions" - "Previous 24h Transactions") / "Previous 24h Transactions") * 100 as "change (%) Transactions",

count(distinct tx_from) as "24h Active Wallets",

lag("24h Active Wallets",1) over(order by "Day") as "Previous 24h Active Wallets",

(("24h Active Wallets" - "Previous 24h Active Wallets") / "Previous 24h Active Wallets") * 100 as "change (%) Active Wallets",

(sum(iff(tx_succeeded = TRUE, 1, 0)) / "24h Transactions") * 100 as "24h Success Rate",

lag("24h Success Rate",1) over(order by "Day") as "Previous 24h Success Rate",

(("24h Success Rate" - "Previous 24h Success Rate") / "Previous 24h Success Rate") * 100 as "change (%) Success Rate"

from osmosis.core.fact_transactions

where "Day" >= current_date - 2 and "Day" < current_date

group by 1

),

change_fee as (

select

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

avg((substring (fee,0,charindex('uosmo',fee)-1)/pow(10,6)) * osmo_usd) as "24h TX Fee",

lag("24h TX Fee",1) over(order by "Day") as "Previous 24h TX Fee",

(("24h TX Fee" - "Previous 24h TX Fee") / "Previous 24h TX Fee") * 100 as "change (%) TX Fee"