select

date_trunc('day',RECORDED_HOUR) as date,

avg(CLOSE) as usdprice

from crosschain.core.fact_hourly_prices

where ID ilike 'terra-luna-2'

group by 1

),

main as (select date_trunc('day',block_timestamp) as date,

case when block_timestamp < '2023-01-01' then 'Before Start of 2023'

else 'After Start of 2023' end as timeframe,

action as type,

count (distinct tx_id) as Transactions,

count(DISTINCT DELEGATOR_ADDRESS) as users,

sum(amount) as total_luna_volume

from terra.core.ez_staking

where tx_succeeded = 'TRUE'

and block_timestamp >= '2022-12-20'

group by 1,2,3)

select a.date,

timeframe,

type,

Transactions,

users,

total_luna_volume,

total_luna_volume*usdprice as total_usd_volume

from main a

join luna_price b on a.date=b.date