select

timestamp::date as day,

avg(price_usd) as flow_price

from flow.core.fact_prices

where symbol = 'FLOW'

group by 1

),

base as (

select

*,

split_part(marketplace , '.' , 3) as marketplaces,

case when currency ilike '%flow%' then price * flow_price else price end as nft_price

from flow.core.ez_nft_sales join price on block_timestamp::date = day

where

nft_collection = 'A.e4cf4bdc1751c65d.AllDay' and tx_succeeded = 'TRUE'

select

count(distinct tx_id) as sales_count,

count(distinct buyer) as unique_buyer,

count(distinct seller) as unique_seller,

sum(nft_price) as sales_volume,

avg(nft_price) as avg_nft_price,

median(nft_price) as median_nft_price

from base