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

DATE_TRUNC('day', block_timestamp) AS trade_date,

sum(nft_Price_usd) as sale_volume_usd,

sum(sale_volume_usd) OVER (ORDER BY trade_date) AS Cumulative_Volume

from nft_txns

Group By 1

ORDER BY 1 DESC