with price as (

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

),

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 terra.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(fee * price) 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"

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