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')

,

tb2 as ( select EVENT_DATA:offerAddress::string as buyer,

EVENT_DATA:offerAmount::float as sales_amount,

'A.0b2a3299cc857e29.TopShot.NFT' as collection,

EVENT_DATA:nftId::integer as nft_id,

EVENT_DATA:paymentVaultType as token,

tx_id,

trunc(block_timestamp,'day') as day

from tb1

where event_type = 'OfferCompleted'

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

,

tb3 as (select EVENT_DATA:buyer::string as buyer,

EVENT_DATA:salePrice::float as sales_amount,

EVENT_DATA:nftType::string as collection,

EVENT_DATA:nftID::integer as nft_id,

EVENT_DATA:salePaymentVaultType::string as token,

tx_id,

trunc(block_timestamp,'day') as day

from tb1

where event_type = 'ListingCompleted')

,

tb4 as ( select trunc(hour,'day') as day,

avg(price) as avg_price

from flow.price.ez_prices_hourly

where symbol = 'FLOW'

group by 1

,

tb5 as ( select a.day,