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,

'Flowty' as marketplace,

event_data['storefrontAddress'] as seller,

event_data['buyer'] as buyer,

event_data['salePrice'] * price_usd as amount_usd

from flow.core.fact_events a

left 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 in ('ListingCompleted')

and tx_succeeded = 1

and event_data:purchased = 'true'

union all

select

tx_id,

block_timestamp,

'Flowty' as marketplace,

event_data['acceptingAddress'] as seller,

event_data['offerAddress'] as buyer,

event_data['offerAmount'] * price_usd as amount_usd

from flow.core.fact_events a

left join t1 on block_timestamp::date = t1.date and split(replace(parse_json(event_data['paymentVaultType']):type:type:typeID::string, '.Vault', ''), '{')[0] = t1.token_contract