avg(CLOSE) as price

from

crosschain.core.fact_hourly_prices

where

ID ilike 'terra-luna-2'

and

RECORDED_HOUR::date between '2023-01-01' and CURRENT_DATE - 5

group by 1)

,raw as (select

t.*,

SALES_AMOUNT*price/1e6 AS usd_Volume

from terra.core.fact_nft_sales t , prices p

where BLOCK_TIMESTAMP::date = date

and CURRENCY='uluna')

select

date_trunc('day', BLOCK_TIMESTAMP)::date AS DAY,

iff(BLOCK_TIMESTAMP::date = '2023-01-09', 'Jan-9th', 'Other') as type,

count(distinct TX_ID) AS txns,

count(distinct TOKEN_ID) AS tokens_sold,

count(distinct MARKETPLACE) AS MARKETPLACEs,

count(distinct PURCHASER) AS act_users,

sum(usd_Volume) AS volume,

volume/act_users as volume_per_sender,

volume/tokens_sold as volume_per_token,

volume/MARKETPLACEs as volume_per_MARKETPLACE,

volume/txns as volume_per_tx,

--volume/count(distinct BLOCK_TI--MESTAMP::date ) as volume_per_day,

txns/act_users as txs_per_user

--txns/count(distinct BLOCK_TIMESTAMP::date ) as txs_per_day,

-- act_users/count(distinct BLOCK_TIMESTAMP::date ) as wallets_per_day