avg(CLOSE) as price

from

crosschain.core.fact_hourly_prices

where

ID ilike 'terra-luna-2'

and

RECORDED_HOUR ilike '2023%'

group by 1)

,raw as (select

t.*,

AMOUNT*price/1e6 AS usd_Volume

from terra.core.ez_transfers t , prices p

where BLOCK_TIMESTAMP::date = date

and CURRENCY='uluna')

select

BLOCK_TIMESTAMP::date AS date,

'to Defi' as type ,

count(TX_id) AS txns,

count(distinct sender) AS act_users,

sum(usd_Volume) AS volume,

volume/txns as volume_per_tx,

txns/act_users as txs_per_user

from raw

where receiver in (select address from terra.core.dim_address_labels where label_type = 'defi')

group by 1

union all

select

BLOCK_TIMESTAMP::date AS date,

'from Defi' as type ,

count(TX_id) AS txns,

count(distinct receiver) AS act_users,

sum(usd_Volume) AS volume,