with act1 as (

with tab1 as (

select BLOCK_TIMESTAMP::date as date,MINT,SALES_AMOUNT,TX_ID

,case

when date>='2022-11-07' and date<'2022-11-14'then 'one week after ftx chaos'

when date>='2022-10-31' and date<'2022-11-07'then 'one week before ftx chaos' end as chaos_time

from solana.core.fact_nft_sales

where (MARKETPLACE='magic eden v1' or MARKETPLACE='magic eden v2')

and chaos_time is not null

group by 1,2,3,4),

tab2 as (

select mint,PROJECT_NAME

from solana.core.dim_nft_metadata)

select date,PROJECT_NAME,count(distinct TX_ID)as sales_count,chaos_time

from tab1

left join tab2

on tab1.mint=tab2.mint

group by 1,2,4)

select date,PROJECT_NAME,sales_count,chaos_time,rank()over(partition by date order by sales_count desc) as rank

from act1)

select date,PROJECT_NAME,sales_count,chaos_time

from task1

where rank<=11

and PROJECT_NAME is not null