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,

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

),

All_Traders AS (

SELECT buyer AS wallet, DATE_TRUNC('week', block_timestamp) AS trade_week FROM NFT_TXNS

UNION

SELECT seller AS wallet, DATE_TRUNC('week', block_timestamp) AS trade_week FROM NFT_TXNS

),

First_Trade AS (

SELECT wallet, MIN(trade_week) AS first_trade_week