priceTb as (

select

trunc(HOUR,'day') as p_date,

avg(PRICE) as avg_price

from flow.price.ez_prices_hourly

where SYMBOL ilike 'Flow'

group by 1

, mainTb as (

select *

from flow.core.fact_events

where event_type in ('OfferCompleted','ListingCompleted')

and event_contract in ('A.3cdbb3d569211ff3.NFTStorefrontV2','A.b8ea91944fd51c43.OffersV2')

and tx_succeeded = 'true'

and event_data:purchased = 'true'

,

OfferCompleted_Tb as (

select

trunc(block_timestamp,'day') as date,

tx_id,

EVENT_DATA:offerAddress::string as buyer,

EVENT_DATA:acceptingAddress::string as seller,

EVENT_DATA:offerAmount::float as sales_amount,

EVENT_DATA:offerType::string as collection,

EVENT_DATA:nftId::integer as nft_id,

EVENT_DATA:paymentVaultType as token,

case when token ilike '%flow%' then sales_amount*avg_price else sales_amount end as volume

from mainTb a

left outer join priceTb b on trunc(a.block_timestamp,'day') = b.p_date

where event_type = 'OfferCompleted'

AND event_data::string like '%0x6590f8918060ef13%'

,

ListingCompleted_Tb as (