select address as mint,

label

from solana.core.dim_labels b

where label in ('mad lads')

-- label in ('solana monkey business', 'mad lads')

-- NFT_COLLECTION_NAME in ('Mad Lads', 'Solana Monkey Business', 'Tensorians', 'DeGods')

, sol_price as (

select hour::date as price_date,

avg(price) as price

from solana.price.ez_prices_hourly

where token_address = 'So11111111111111111111111111111111111111112'

group by 1

, base as (

SELECT

date_trunc('day', a.block_timestamp) as date,

case when label is null then 'Others' else label end as collection,

count(a.tx_id) as sale_count,

count(distinct purchaser) as unique_buyer,

sum(sales_amount) as volume,

min(sales_amount) as floor_price,

median(sales_amount) as median_price,

median(sales_amount * price) as median_price_usd,

-- sum(sale_count) over (partition by collection order by date) as cumu_count,

-- sum(unique_buyer) over (partition by collection order by date) as cumu_buyer,

-- sum(volume) over (partition by collection order by date) as cumu_volume,

avg(median_price) OVER (partition by collection ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as median_PRICE_MA,

avg(volume) OVER (partition by collection ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as volume_MA,

avg(unique_buyer) OVER (partition by collection ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as unique_buyer_MA,

avg(sale_count) OVER (partition by collection ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as count_MA,

100 * RATIO_TO_REPORT(volume) OVER (PARTITION BY date) AS percent_share

FROM solana.nft.fact_nft_sales a