SELECT

HOUR::date AS Date,

token_address,

avg(price) AS price_usd

FROM ronin.price.ez_prices_hourly

GROUP BY 1,2

),

NFT_TXNS AS (

SELECT

block_timestamp,

tx_hash,

from_address AS seller,

to_address AS buyer,

name AS collection,

decoded_log:acceptedSettlePrice / 1e18 as nft_price,

(decoded_log:acceptedSettlePrice / 1e18)*price_usd as nft_Price_usd

from ronin.nft.ez_nft_transfers tr

join ronin.core.ez_decoded_event_logs log using (tx_hash, block_timestamp)

left join price p on block_timestamp::date = p.date and log.decoded_log:settleToken = token_address

where event_name = 'OrderMatched'

and decoded_log:order[0]:extraData[0][2] = token_id

and tx_succeeded

and block_timestamp::date >= CURRENT_DATE - INTERVAL '30 days'

Collection,

count(distinct tx_hash) as n_sales,

count(distinct buyer) as buyers,

count(distinct seller) as sellers,

sum(nft_Price_usd) as sales_volume_usd

from nft_txns

group by 1

order by n_sales desc