with mintable as ( select nft_address, min (date_trunc(month,block_timestamp)) as mindate from ethereum.core.ez_nft_sales where block_timestamp::date >= '2022-01-01' and block_timestamp::date < '2023-01-01' group by 1), maxtable as ( select nft_address, max (date_trunc(month,block_timestamp)) as maxdate from ethereum.core.ez_nft_sales where block_timestamp::date >= '2022-01-01' and block_timestamp::date < '2023-01-01' group by 1), previous_table as ( select t1.nft_address, coalesce (initcap(project_name),initcap(address_name),initcap(t1.nft_address)) as Project_Title, median (price_usd) as Previous_Price from ethereum.core.ez_nft_sales t1 left outer join ethereum.core.dim_labels t2 on t1.nft_address = t2.address join mintable t3 on t1.nft_address = t3.nft_address where price_usd > 0 and date_trunc(month,block_timestamp) = mindate group by 1,2), maintable as ( select Project_Title, count (distinct tx_hash) as Sales_Count, (avg(price_usd - Previous_Price)/avg(Previous_Price)) * 100 as Price_Change_Ratio from ethereum.core.ez_nft_sales t1 join previous_table t2 on t1.nft_address = t2.nft_address join maxtable t3 on t1.nft_address = t3.nft_address where block_timestamp::date >= '2022-01-01' and block_timestamp::date < '2023-01-01' and date_trunc(month,block_timestamp) = maxdate and price_usd > 0 group by 1 having sales_count > 10) select case when Price_Change_Ratio > 0 and price_change_ratio < 10 then 'Gained Less Than %10 Value' when Price_Change_Ratio > 10 and price_change_ratio < 25 then 'Gained %10 - %25 Value' when Price_Change_Ratio > 25 and price_change_ratio < 50 then 'Gained %25 - %50 Value' when Price_Change_Ratio > 50 and price_change_ratio < 75 then 'Gained %50 - %75 Value' when Price_Change_Ratio > 75 and price_change_ratio < 100 then 'Gained %75 - %100 Value' when price_change_ratio > 100 then 'Gained More Than %100 Value' when price_change_ratio = 0 then 'Price Did Not Change' when price_change_ratio < 0 and price_change_ratio > -10 then 'Lost Less Than %10 Value' when price_change_ratio < -10 and price_change_ratio > -25 then 'Lost %10 - %25 Value' when price_change_ratio < -25 and price_change_ratio > -50 then 'Lost %25 - %50 Value' when price_change_ratio < -50 and price_change_ratio > -75 then 'Lost %50 - %75 Value' when price_change_ratio < -75 and price_change_ratio > -100 then 'Lost %75 - %100 Value' end as type, count (distinct project_Title) as NFTs_Count from maintable group by 1 order by 2 desc

NFTs in 2022