t1 as (

timestamp::date as date,

token,

token_contract,

avg(price_usd) as price_usd

from flow.price.fact_prices

group by 1, 2, 3

),

t2 as (

select

tx_id,

block_timestamp,

event_data['storefrontAddress'] as seller,

event_data['buyer'] as buyer,

event_data['nftType']::string as collection,

split(collection, '.')[2] as collection_name,

event_data['nftID'] as nft_id,

event_data['salePrice'] as amount,

amount * price_usd as amount_usd

from flow.core.fact_events a

join t1 on block_timestamp::date = t1.date and replace(event_data['salePaymentVaultType'], '.Vault', '') = t1.token_contract

where event_contract = 'A.3cdbb3d569211ff3.NFTStorefrontV2'

and event_type = 'ListingCompleted'

and event_data:purchased = 'true'

and tx_succeeded = 1

union all

select

tx_id,

block_timestamp,

event_data['acceptingAddress'] as seller,