pricet as (

select

hour::date as date,

token_address,

symbol,

avg(price) as token_price_usd

from ronin.price.ez_prices_hourly

group by 1, 2, 3

),

txns as (

select

tx_hash,

block_timestamp,

b.from_address as seller_address,

b.to_address as buyer_address,

b.contract_address as collection_id,

b.name as collection_name,

pricet.symbol,

(a.decoded_log:acceptedSettlePrice / 1e18) as price,

price * token_price_usd as price_usd,

quantity,

token_id,

a.decoded_log:settleToken as token_address

from ronin.core.ez_decoded_event_logs a

join ronin.nft.ez_nft_transfers b using (tx_hash, block_timestamp)

left join pricet on block_timestamp::date = date and a.decoded_log:settleToken = token_address

where event_name = 'OrderMatched'

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

and decoded_log::string ilike '%' || from_address || '%'

and tx_succeeded

),