with mints as (

select BLOCK_TIMESTAMP::Date as block_timestamp, ASSET_SENDER, TX_ID, RECEIVER, b.ASSET_ID, ASSET_NAME

from flipside_prod_db.algorand.transfers a

JOIN flipside_prod_db.algorand.asset b

on a.ASSET_ID = b.ASSET_ID

where b.creator_address = 'X6MNR4AVJQEMJRHAPZ6F4O4SVDIYN67ZRMD2O3ULPY4QFMANQNZOEYHODE'

and asset_sender = 'X6MNR4AVJQEMJRHAPZ6F4O4SVDIYN67ZRMD2O3ULPY4QFMANQNZOEYHODE'

and asset_sender <> a.receiver

and a.receiver <> 'X6MNR4AVJQEMJRHAPZ6F4O4SVDIYN67ZRMD2O3ULPY4QFMANQNZOEYHODE'

and a.AMOUNT > 0

and b.asset_Name not like 'test%'),

secondary as (

select BLOCK_TIMESTAMP::date as block_timestamp, ASSET_SENDER, TX_ID, RECEIVER, b.ASSET_ID, ASSET_NAME

from flipside_prod_db.algorand.transfers a

JOIN flipside_prod_db.algorand.asset b

on a.ASSET_ID = b.ASSET_ID

where b.creator_address = 'X6MNR4AVJQEMJRHAPZ6F4O4SVDIYN67ZRMD2O3ULPY4QFMANQNZOEYHODE'

and asset_sender <> 'X6MNR4AVJQEMJRHAPZ6F4O4SVDIYN67ZRMD2O3ULPY4QFMANQNZOEYHODE'

and asset_sender <> a.receiver

and a.receiver <> 'X6MNR4AVJQEMJRHAPZ6F4O4SVDIYN67ZRMD2O3ULPY4QFMANQNZOEYHODE'

and a.AMOUNT > 0

and b.asset_Name not like 'test%'),

topshot as(

SELECT

block_timestamp::date as block_timestamp,

COUNT(DISTINCT tx_id) as sales,

sum(price) as sales_volume,

avg(price) as average_price,

COUNT(DISTINCT buyer) as buyers,

min(price) as floor_price

FROM flow.core.ez_nft_sales

WHERE split(nft_collection, '.')[2] LIKE 'TopShot'

AND block_timestamp::date between '2022-09-22' and CURRENT_DATE()