select ADDRESS

from solana.core.dim_labels

where LABEL like'%lily%'

and LABEL_TYPE='nft'

and LABEL_SUBTYPE='nf_token_contract'

and ADDRESS_NAME like'%lily%'),

tab2 as (

select

MARKETPLACE

,MINT

,PURCHASER

,SELLER

,SALES_AMOUNT

,TX_ID

from solana.core.fact_nft_sales

where SUCCEEDED='true'

and BLOCK_TIMESTAMP::date>='2023-01-01')

select PURCHASER

,zeroifnull(count(distinct TX_ID)) as sale_count

,zeroifnull(count(distinct MINT)) as bought_token

,zeroifnull(sum(SALES_AMOUNT)) as sale_volume

from tab1

left join tab2

on tab1.ADDRESS=tab2.MINT

group by 1

order by 4 desc

limit 10