with tab1 as (

SELECT min(BLOCK_TIMESTAMP::date) as date,PURCHASER,SALES_AMOUNT

FROM solana.core.fact_nft_sales

where SALES_AMOUNT is not null

and SALES_AMOUNT>0

and MARKETPLACE='opensea'

group by 2,3)

select PURCHASER,sum(SALES_AMOUNT)as buy_volume

from tab1

where date>='2022-10-31' and date<='2022-11-07'

group by 1

order by 2 desc

limit 311),

act2 as (

with tab1 as (

SELECT max(BLOCK_TIMESTAMP::date) as date,seller,SALES_AMOUNT

FROM solana.core.fact_nft_sales

where SALES_AMOUNT is not null

and SALES_AMOUNT>0

and MARKETPLACE='opensea'

group by 2,3)

select seller,sum(SALES_AMOUNT)as sale_volume

from tab1

where date>'2022-11-07' and date<='2022-11-14'

group by 1

order by 2 desc

limit 311)

case

when PURCHASER =seller then 'sell'

when PURCHASER<>seller then 'holder' end as hold_st

, count(distinct seller) as nft_sellers

, case

when hold_st is null then 'Hold Their NFT'

when hold_st is not null then 'Sell Their NFT'end as hold_st