b.PROJECT_NAME as nft,

sum(sales_amount) as total_sale,

count(tx_id) as usages,

total_sale/usages as average_sale

from solana.fact_nft_sales s LEFT OUTER JOIN solana.dim_nft_metadata b ON s.mint = b.mint

-- and nft in ('DeGods','Degen Ape Academy','Shadowy Super Coders')

and succeeded='TRUE'

group by 1,2 having nft is not null

)

nft,

lag(block_timestamp, 1) ignore nulls over (order by block_timestamp asc) as date_buy,

datediff('day',date_buy, block_timestamp)as holds

from nft group by 1,block_timestamp)

select nft , avg(holds) * 10000 as average_day from tt group by 1