with agg as (with transfers as ((SELECT NFT_TO_ADDRESS as wallet, 'mint' as action, 1 as value FROM ethereum.core.ez_nft_transfers where NFT_ADDRESS = '0x34d85c9cdeb23fa97cb08333b511ac86e1c4e258' and NFT_FROM_ADDRESS = '0x0000000000000000000000000000000000000000') union all (SELECT NFT_TO_ADDRESS AS wallet, 'gain' AS action, 1 AS value FROM ethereum.core.ez_nft_transfers WHERE NFT_ADDRESS = '0x34d85c9cdeb23fa97cb08333b511ac86e1c4e258' AND NFT_FROM_ADDRESS != '0x0000000000000000000000000000000000000000') UNION ALL (SELECT NFT_FROM_ADDRESS AS wallet, 'lose' AS action, -1 AS value FROM ethereum.core.ez_nft_transfers WHERE NFT_ADDRESS = '0x34d85c9cdeb23fa97cb08333b511ac86e1c4e258' AND NFT_FROM_ADDRESS != '0x0000000000000000000000000000000000000000')) SELECT wallet, SUM(value) AS num_tokens, concat(ROUND(SUM(value)::numeric/10762*100,2),'%') AS ownership FROM transfers GROUP BY wallet ORDER BY num_tokens DESC) SELECT CASE WHEN num_tokens = 0 THEN '0 ⛰' WHEN num_tokens = 1 THEN '1 ⛰' WHEN num_tokens = 2 THEN '2 ⛰' WHEN num_tokens = 3 THEN '3 ⛰' WHEN num_tokens = 4 THEN '4 ⛰' WHEN num_tokens = 5 THEN '5 ⛰' WHEN 6 <= num_tokens AND num_tokens <= 7 THEN '6-7 ⛰' WHEN 8 <= num_tokens AND num_tokens <= 10 THEN '8-10 ⛰' WHEN 11 <= num_tokens AND num_tokens <= 15 THEN '11-15 ⛰' WHEN 16 <= num_tokens AND num_tokens <= 20 THEN '16-20 ⛰' WHEN 21 <= num_tokens AND num_tokens <= 40 THEN '21-40 ⛰' WHEN 41 <= num_tokens AND num_tokens <= 60 THEN '41-60 ⛰' WHEN 61 <= num_tokens AND num_tokens <= 100 THEN '61-100 ⛰' WHEN 101 <= num_tokens THEN '100+ ⛰' END AS num_tokens_bucket, CASE WHEN num_tokens = 0 THEN -1 WHEN num_tokens = 1 THEN 1 WHEN num_tokens = 2 THEN 2 WHEN num_tokens = 3 THEN 3 WHEN num_tokens = 4 THEN 4 WHEN num_tokens = 5 THEN 5 WHEN 6 <= num_tokens AND num_tokens <= 7 THEN 6 WHEN 8 <= num_tokens AND num_tokens <= 10 THEN 6.5 WHEN 11 <= num_tokens AND num_tokens <= 15 THEN 7 WHEN 16 <= num_tokens AND num_tokens <= 20 THEN 7.5 WHEN 21 <= num_tokens AND num_tokens <= 40 THEN 8 WHEN 41 <= num_tokens AND num_tokens <= 60 THEN 9 WHEN 61 <= num_tokens AND num_tokens <= 100 THEN 10 WHEN 100 <= num_tokens THEN 11 END AS num_tokens_order, COUNT(wallet) AS "Wallets Count" FROM agg WHERE num_tokens > 0 GROUP BY num_tokens_bucket,num_tokens_order ORDER BY num_tokens_order ASC

Otherdeed for Otherside by Yuga Labs