Query editor
WITH eth_transfers AS (SELECT "from" as address, -CAST(value AS int256) as amount FROM wlfi_ethereum.stablecoin_evt_transfer WHERE contract_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d UNION ALL SELECT "to" as address, CAST(value AS int256) as amount FROM wlfi_ethereum.stablecoin_evt_transfer WHERE contract_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d), eth_balances AS (SELECT address, SUM(amount) as balance FROM eth_transfers GROUP BY address HAVING SUM(amount) > 0 AND address <> 0x0000000000000000000000000000000000000000), bnb_transfers AS (SELECT "from" as address, -CAST(value AS int256) as amount FROM erc20_bnb.evt_transfer WHERE contract_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d UNION ALL SELECT "to" as address, CAST(value AS int256) as amount FROM erc20_bnb.evt_transfer WHERE contract_address = 0x8d0d000ee44948fc98c9b98a4fa4921476f08b0d), bnb_balances AS (SELECT address, SUM(amount) as balance FROM bnb_transfers GROUP BY address HAVING SUM(amount) > 0 AND address <> 0x0000000000000000000000000000000000000000), sol_balances AS (SELECT COALESCE(from_owner, from_token_account) as address, SUM(CASE WHEN action = 'transfer' AND from_owner IS NOT NULL THEN -CAST(amount AS int256) ELSE CAST(0 AS int256) END) as balance FROM tokens_solana.transfers WHERE token_mint_address = 'USD1ttGY1N17NEEHLmELoaybftRBUSErhqYiQzvEmuB' GROUP BY COALESCE(from_owner, from_token_account) UNION ALL SELECT COALESCE(to_owner, to_token_account) as address, SUM(CASE WHEN action = 'transfer' AND to_owner IS NOT NULL THEN CAST(amount AS int256) ELSE CAST(0 AS int256) END) as balance FROM tokens_solana.transfers WHERE token_mint_address = 'USD1ttGY1N17NEEHLmELoaybftRBUSErhqYiQzvEmuB' GROUP BY COALESCE(to_owner, to_token_account)), sol_balances_agg AS (SELECT address, SUM(balance) as balance FROM sol_balances GROUP BY address HAVING SUM(balance) > 0), eth_supply AS (SELECT 'Ethereum' as chain, CAST(SUM(balance) AS double) / 1e18 as total_supply, COUNT(*) as holder_count FROM eth_balances), bnb_supply AS (SELECT 'BNB Chain' as chain, CAST(SUM(balance) AS double) / 1e18 as total_supply, COUNT(*) as holder_count FROM bnb_balances), sol_supply AS (SELECT 'Solana' as chain, CAST(SUM(balance) AS double) / 1e6 as total_supply, COUNT(*) as holder_count FROM sol_balances_agg), eth_top AS (SELECT 'Ethereum' as chain, SUM(CASE WHEN rn <= 10 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top10_pct, SUM(CASE WHEN rn <= 25 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top25_pct, SUM(CASE WHEN rn <= 100 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top100_pct FROM (SELECT balance, ROW_NUMBER() OVER (ORDER BY balance DESC) as rn FROM eth_balances)), bnb_top AS (SELECT 'BNB Chain' as chain, SUM(CASE WHEN rn <= 10 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top10_pct, SUM(CASE WHEN rn <= 25 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top25_pct, SUM(CASE WHEN rn <= 100 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top100_pct FROM (SELECT balance, ROW_NUMBER() OVER (ORDER BY balance DESC) as rn FROM bnb_balances)), sol_top AS (SELECT 'Solana' as chain, SUM(CASE WHEN rn <= 10 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top10_pct, SUM(CASE WHEN rn <= 25 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top25_pct, SUM(CASE WHEN rn <= 100 THEN CAST(balance AS double) ELSE 0 END) / NULLIF(SUM(CAST(balance AS double)), 0) * 100 as top100_pct FROM (SELECT balance, ROW_NUMBER() OVER (ORDER BY balance DESC) as rn FROM sol_balances_agg)) SELECT s.chain, s.total_supply, s.holder_count, t.top10_pct, t.top25_pct, t.top100_pct FROM (SELECT * FROM eth_supply UNION ALL SELECT * FROM bnb_supply UNION ALL SELECT * FROM sol_supply) s LEFT JOIN (SELECT * FROM eth_top UNION ALL SELECT * FROM bnb_top UNION ALL SELECT * FROM sol_top) t ON s.chain = t.chain ORDER BY s.total_supply DESC