select case when coalesce (initcap(project_name),initcap(address_name)) ilike '%claynosaurz%' then 'Claynosaurz'

else coalesce (initcap(project_name),initcap(address_name),t1.mint) end as Collection_Name,

sum (sales_amount) as Total_Sales_Volume

from solana.core.fact_nft_sales t1 left outer join solana.core.dim_labels t2 on t1.mint = t2.address

left outer join solana.core.dim_nft_metadata t3 on t1.mint = t3.mint

where succeeded ilike 'TRUE'

and marketplace ilike 'tensorswap'

group by 1

order by 2 DESC

limit 100),

Listtable as (

select t1.block_timestamp::date as date,

t1.tx_id,

t1.signers[0] as Lister,

t2.value:accounts[2] as Mint_Address

from solana.core.fact_transactions t1 join lateral flatten (input => instructions) t2

where t2.value:programId = 'TSWAPaqyCSx2KABk68Shruf4rp7CxcNi8hAsbdwmHbN'

and array_contains('Program log: Instruction: List'::VARIANT,log_messages)

and succeeded = 'TRUE'

and block_timestamp::date >= '2023-03-06'),

Collectiontable as (

select t1.*,

coalesce (initcap(project_name),initcap(address_name),t1.Mint_Address) as Collection_Name

from Listtable t1 left join solana.core.dim_labels t2 on t1.Mint_Address = t2.address

left join solana.core.dim_nft_metadata t3 on t1.Mint_Address = t3.mint),

listers as (

select lister,

count (distinct tx_id) as Lists_Count,

count (distinct Lister) as Listers_Count

from Collectiontable

where collection_name in (select distinct collection_name from topcollections)

group by 1),