from flow.core.fact_hourly_prices

where token = 'Flow'

and RECORDED_HOUR::date >= '2023-01-01'

group by 1)

,

market as ( select date(block_timestamp) as date,nft_collection, tx_id, nft_id, buyer, seller,

case when currency in ('A.1654653399040a61.FlowToken','A.ead892083b3e2c6c.FlowUtilityToken') then price*avg_price

else price end as volume

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

where block_timestamp::date >= '2023-01-01'

and marketplace in ('A.4eb8a10cb9f87357.NFTStorefront','A.4eb8a10cb9f87357.NFTStorefrontV2')

,

buyers as ( select buyer, CONTRACT_NAME, count(DISTINCT(nft_collection)) as total_collection, count(DISTINCT(nft_id)) as nfts,

count(DISTINCT(tx_id)) as sales, count(DISTINCT(seller)) as sellers, count(DISTINCT(buyer)) as buyers, sum(volume) as usd_volume,

avg(volume) as avg_price, max(volume) as max_price, median(volume) as median_price

from market a join flow.core.dim_contract_labels b on a.nft_collection = b.event_contract

where contract_name != 'Basketballs'

group by 1,2)

,

sellers as ( select seller, CONTRACT_NAME, count(DISTINCT(nft_collection)) as total_collection, count(DISTINCT(nft_id)) as nfts,

count(DISTINCT(tx_id)) as sales, count(DISTINCT(seller)) as sellers, count(DISTINCT(buyer)) as buyers, sum(volume) as usd_volume,

avg(volume) as avg_price, max(volume) as max_price, median(volume) as median_price

from market a join flow.core.dim_contract_labels b on a.nft_collection = b.event_contract

where contract_name != 'Basketballs'

group by 1,2)

,

final as ( select buyer, a.contract_name, b.usd_volume-a.usd_volume as profit

from buyers a join sellers b on a.buyer = b.seller and a.contract_name = b.contract_name

where a.sales = b.sales)

select contract_name, sum(profit) as profits

from final

group by 1

order by 2 desc