avg(price) as avg_price

from flow.price.ez_prices_hourly

where symbol = 'FLOW'

group by 1

,

base as (select block_timestamp,

tx_id,

nft_id,

buyer,

price,

seller,

case when currency ilike '%flow%' then price*avg_price else price end as price_usd

from flow.nft.ez_nft_sales a left outer join price b on a.block_timestamp::date = b.date

where nft_collection = 'A.0b2a3299cc857e29.TopShot'

,

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

,

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

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,

tx_id,

trunc(block_timestamp,'day') as day

from base1

where event_type = 'OfferCompleted'

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

,