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 date_trunc('week',BLOCK_TIMESTAMP) as week

,MARKETPLACE

,MINT

,PURCHASER

,SELLER

,SALES_AMOUNT

,TX_ID

from solana.core.fact_nft_sales

where SUCCEEDED='true'

and week>='2023-01-01')

select week

,MARKETPLACE

,zeroifnull(count(distinct PURCHASER)) as buyers

,zeroifnull(count(distinct SELLER)) as sellers

,zeroifnull(count(distinct TX_ID)) as sale_count

,zeroifnull(count(distinct MINT)) as token_sold

,zeroifnull(sum(SALES_AMOUNT)) as sale_volume

,zeroifnull(avg(SALES_AMOUNT)) as avg_sale_volume

from tab1

left join tab2

on tab1.ADDRESS=tab2.MINT

where week is not null

group by 1,2