select recorded_hour::date as day,

avg (close) as Flowprice

from flow.core.fact_hourly_prices

where token ilike 'flow'

group by 1),

maintable as (

select block_timestamp::date as date,

case when date < '2023-04-15' then 'Before NBA Playoffs'

else 'After Beginning of NBA Playoffs' end as timespan,

'Topshot Marketplace' as type,

count (distinct tx_id) as Sales_Count,

count (distinct nft_id) as Tokens_Count,

count (Distinct buyer) as Purchasers,

count (distinct seller) as Sellers,

sum (case when currency = 'A.1654653399040a61.FlowToken' then price*flowprice else price end) as Sales_Volume,

avg (case when currency = 'A.1654653399040a61.FlowToken' then price*flowprice else price end) as Average_Volume,

median (case when currency = 'A.1654653399040a61.FlowToken' then price*flowprice else price end) as Median_Volume,

min (case when currency = 'A.1654653399040a61.FlowToken' then price*flowprice else price end) as Minimum_Volume,

Max (case when currency = 'A.1654653399040a61.FlowToken' then price*flowprice else price end) as Maximum_Volume

from flow.core.ez_nft_sales t1 join flowpricet t2 on t1.block_timestamp::Date = t2.day

where marketplace ilike '%TopShotMarketV3%'

and block_timestamp >= '2023-04-01'

and tx_succeeded ilike 'true'

group by 1,2,3

union all

select block_timestamp::date as date,

case when date < '2023-04-15' then 'Before NBA Playoffs'

else 'After Beginning of NBA Playoffs' end as timespan,

'Topshot NFT Collection' as type,

count (distinct tx_id) as Sales_Count,

count (distinct nft_id) as Tokens_Count,

count (Distinct buyer) as Purchasers,