SELECT

project_name as project,

ZEROIFNULL(round(sum(CASE when block_timestamp::date = dateadd('d', -1, current_date) then price_usd end),2)) as "Volume(24h) $",

ZEROIFNULL(round(sum(CASE when block_timestamp::date >= dateadd('week', -1, current_date) then price_usd end),2)) as "Volume(7d) $",

ZEROIFNULL(round(sum(CASE when block_timestamp::date >= dateadd('month', -1, current_date) then price_usd end),2)) as "Volume(1month) $",

round(sum(price_usd), 2) as "Total volume $",

min(CASE when currency_symbol IN ('AVAX', 'WAVAX') AND price != 0 AND block_timestamp::date = dateadd('d', -1, current_date) then price end) as "Floor price [AVAX]",

round(sum(total_fees_usd), 2) as "Total fee $",

count(DISTINCT tx_hash) as "Number of Sales",

count(DISTINCT tokenid) as "Number of NFT sold",

count(DISTINCT buyer_address) as "Number of Buyers",

count(DISTINCT seller_address) as "Number of Seller"

FROM avalanche.nft.ez_nft_sales

WHERE project_name IS NOT NULL

GROUP BY 1

),

last_nft_holders as (

SELECT

-- case when nft_from_address <> '0x0000000000000000000000000000000000000000' then nft_from_address else nft_to_address end as holder,

nft_to_address as holder,

project_name as project,

ROW_NUMBER() over (PARTITION BY nft_address, tokenid ORDER BY block_timestamp DESC) as transaction_rank

FROM avalanche.nft.ez_nft_transfers

where nft_to_address <> '0x0000000000000000000000000000000000000000'

,

holders as (

SELECT

project,

count(DISTINCT holder) as last_N_holders,

count(holder) as total_supply

FROM last_nft_holders

WHERE transaction_rank = 1

GROUP BY 1