with famoust as ( select * from solana.core.dim_labels where label = 'famous fox federation'), transdimensionalt as ( select * from solana.core.dim_labels where label = 'transdimensional fox federation'), famoust1 as ( select t2.tx_id, t1.block_timestamp as mint_date, t2.block_timestamp as purchase_date, t1.purchaser, seller as NFT_User from solana.core.fact_nft_mints t1 join solana.core.fact_nft_sales t2 on t1.purchaser = t2.seller and t1.mint = t2.mint and t2.block_timestamp > t1.block_timestamp where t1.mint in (select address from famoust)), famoust2 as ( select NFT_User, datediff (hour,mint_date,purchase_date) as Date_Diff from famoust1), transdimensionalt1 as ( select t2.tx_id, t1.block_timestamp as mint_date, t2.block_timestamp as purchase_date, t1.purchaser, seller as NFT_User from solana.core.fact_nft_mints t1 join solana.core.fact_nft_sales t2 on t1.purchaser = t2.seller and t1.mint = t2.mint and t2.block_timestamp > t1.block_timestamp where t1.mint in (select address from transdimensionalt)), transdimensionalt2 as ( select NFT_User, datediff (hour,mint_date,purchase_date) as Date_Diff from transdimensionalt1) select 'Famous Fox Federation' as collection_name, case when date_diff < 1 then 'Sold in Less Than 1 Hour After Mint' when date_diff >= 1 and date_diff < 12 then 'Sold in 1 - 12 Hours After Mint' when date_diff >= 12 and date_diff < 24 then 'Sold in 12 - 24 Hours After Mint' when date_diff >= 24 and date_diff < 48 then 'Sold in 1 - 2 Days After Mint' when date_diff >= 48 and date_diff < 167 then 'Sold in 2 - 7 Days After Mint' when date_diff >= 167 then 'Sold More Than 1 Week After Mint' end as typee, count (distinct nft_user) as Users_Count from famoust2 group by 1,2 union ALL select 'Transdimensional Fox Federation' as collection_name, case when date_diff < 1 then 'Sold in Less Than 1 Hour After Mint' when date_diff >= 1 and date_diff < 12 then 'Sold in 1 - 12 Hours After Mint' when date_diff >= 12 and date_diff < 24 then 'Sold in 12 - 24 Hours After Mint' when date_diff >= 24 and date_diff < 48 then 'Sold in 1 - 2 Days After Mint' when date_diff >= 48 and date_diff < 167 then 'Sold in 2 - 7 Days After Mint' when date_diff >= 167 then 'Sold More Than 1 Week After Mint' end as typee, count (distinct nft_user) as Users_Count from transdimensionalt2 group by 1,2

The Battle of The Federation Foxes !