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'),

tab3 as (

select

SELLER

,zeroifnull(count(distinct MINT)) as token_sold

,zeroifnull(sum(SALES_AMOUNT)) as sale_volume

from tab1

left join tab2

on tab1.ADDRESS=tab2.MINT

and MARKETPLACE='magic eden v2'

group by 1

order by 2 desc

limit 10),

tab4 as (

select

SELLER

,zeroifnull(count(distinct MINT)) as token_sold

,zeroifnull(sum(SALES_AMOUNT)) as sale_volume

from tab1