with tab1 as (

select ADDRESS,ADDRESS_NAME

from solana.core.dim_labels

where ADDRESS_NAME ilike '%Blockasset Legends%'or

ADDRESS_NAME in('The Suites','Laidback Lions','Hockey Heroes','Collectorz Club: The Collectorz','Sports Rewind')),

tab2 as (

select date_trunc('day',BLOCK_TIMESTAMP) as day,MINT,TX_ID,SELLER,SALES_AMOUNT,PURCHASER

from solana.core.fact_nft_sales

group by 1,2,3,4,5,6)

select tab2.day,ADDRESS_NAME,count(distinct TX_ID) as sales_volume_count,count(distinct SELLER) as unique_sellers,

count(distinct PURCHASER)as unique_buyers,sum(SALES_AMOUNT)as sales_volume_sol,avg(SALES_AMOUNT)as daily_avg

,case

when ADDRESS_NAME ilike'%Blockasset Legends%' then 'Blockasset Legends' else ADDRESS_NAME end as sport_collections

from tab1

left join tab2

on tab1.ADDRESS=tab2.MINT

group by 1,2