--credit : https://app.flipsidecrypto.com/velocity/queries/3f1cf57d-0e81-4c7b-88fa-74c6241182e1 with purchaset1 as ( select purchaser, mint, min (block_timestamp::date) as purchase_date from solana.core.fact_nft_sales t1 where succeeded = 'TRUE' and marketplace = 'exchange art' group by 1,2), salet1 as ( select seller, t1.mint, min (block_timestamp::date) as sale_date from solana.core.fact_nft_sales t1 where succeeded = 'TRUE' and seller in (select purchaser from purchaset1) and mint in (select mint from purchaset1) and marketplace = 'exchange art' group by 1,2), purchaset2 as ( select purchaser, mint, min (block_timestamp::date) as purchase_date from solana.core.fact_nft_sales t1 where succeeded = 'TRUE' and marketplace ilike 'magic eden%' group by 1,2), salet2 as ( select seller, t1.mint, min (block_timestamp::date) as sale_date from solana.core.fact_nft_sales t1 where succeeded = 'TRUE' and seller in (select purchaser from purchaset2) and mint in (select mint from purchaset2) and marketplace ilike 'magic eden%' group by 1,2), maintable as ( select datediff (day,purchase_date, sale_date) as Holding_Time, purchaser, seller, t1.mint from salet1 t1 join purchaset1 t2 on t1.seller = t2.purchaser and t1.mint = t2.mint where Holding_Time >= 0), maintable1 as ( select datediff (day,purchase_date, sale_date) as Holding_Time, purchaser, seller, t1.mint from salet2 t1 join purchaset2 t2 on t1.seller = t2.purchaser and t1.mint = t2.mint where Holding_Time >= 0) select 'Exchange Art' as marketplace1, case when Holding_Time <= 1 then 'Less Than 1 Day' when Holding_Time > 1 and Holding_Time <= 7 then 'Between 1 Day to 1 Week' when Holding_Time > 7 and Holding_Time <= 14 then 'Between 1 Week to 2 Weeks' when Holding_Time > 14 and Holding_Time <= 31 then 'Between 2 Weeks to 1 Month' when Holding_Time > 31 and Holding_Time <= 93 then 'Between 1 Months to 3 Months' when Holding_Time > 93 and Holding_Time <= 180 then 'Between 3 Months to 6 Months' when Holding_Time > 180 and Holding_Time <= 365 then 'Between 6 Months to 1 Year' else 'More Than 1 Year' end as type, count (purchaser) as Users_Count from maintable group by 1,2 union ALL select 'Magic Eden' as marketplace1, case when Holding_Time <= 1 then 'Less Than 1 Day' when Holding_Time > 1 and Holding_Time <= 7 then 'Between 1 Day to 1 Week' when Holding_Time > 7 and Holding_Time <= 14 then 'Between 1 Week to 2 Weeks' when Holding_Time > 14 and Holding_Time <= 31 then 'Between 2 Weeks to 1 Month' when Holding_Time > 31 and Holding_Time <= 93 then 'Between 1 Months to 3 Months' when Holding_Time > 93 and Holding_Time <= 180 then 'Between 3 Months to 6 Months' when Holding_Time > 180 and Holding_Time <= 365 then 'Between 6 Months to 1 Year' else 'More Than 1 Year' end as type, count (purchaser) as Users_Count from maintable1 group by 1,2 order by 3 desc

Everyone Needs a 1-of-1