WITH tiertbl as ( WITH alldata as ( WITH undelegation_database AS ( WITH database AS ( WITH tbl AS ( SELECT * FROM berachain.testnet.fact_event_logs WHERE ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' AND CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' AND ORIGIN_FUNCTION_SIGNATURE = '0xe1f63d2e' AND TX_SUCCEEDED = 'TRUE' ) SELECT block_timestamp, tx_hash, '0x' || SUBSTR(PARSE_JSON(TOPICS) [1] :: string, -40) AS user_wallet, '0x' || SUBSTR(PARSE_JSON(TOPICS) [2] :: string, -40) AS validator_address, utils.udf_hex_to_int(DATA) / pow(10, 18) AS undelegated_amount FROM tbl WHERE user_wallet != '0xce67e15cbcb3486b29ad44486c5b5d32f361fddc' ) SELECT user_wallet, validator_address, SUM(undelegated_amount) AS total_undelegation FROM database GROUP BY 1, 2 ), delegation_database AS ( WITH database AS ( WITH tbl AS ( SELECT * FROM berachain.testnet.fact_event_logs WHERE ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' AND CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' AND ORIGIN_FUNCTION_SIGNATURE = '0x95c0e232' AND TX_SUCCEEDED = 'TRUE' ) SELECT block_timestamp, tx_hash, '0x' || SUBSTR(PARSE_JSON(TOPICS) [1] :: string, -40) AS user_wallet, '0x' || SUBSTR(PARSE_JSON(TOPICS) [2] :: string, -40) AS validator_address, utils.udf_hex_to_int(DATA) / pow(10, 18) AS delegated_amount FROM tbl WHERE user_wallet != '0xce67e15cbcb3486b29ad44486c5b5d32f361fddc' ) SELECT user_wallet, validator_address, SUM(delegated_amount) AS total_delegation FROM database GROUP BY 1, 2 ) SELECT COALESCE(a.user_wallet, b.user_wallet) AS "User address", COALESCE(a.validator_address, b.validator_address) AS "Validator address", COALESCE(a.total_delegation, 0) AS total_delegation, COALESCE(b.total_undelegation, 0) AS total_undelegation, COALESCE(a.total_delegation, 0) - COALESCE(b.total_undelegation, 0) AS total_net_delegation FROM delegation_database a FULL JOIN undelegation_database b ON a.user_wallet = b.user_wallet AND a.validator_address = b.validator_address where "Validator address" = '0x516e5a64adf705c4bb17ee0d0ab440d74346249b' -- caliber ORDER BY total_net_delegation DESC; ) SELECT "User address", sum (total_net_delegation) as "total_net_delegated" from alldata group by 1 ) SELECT CASE WHEN "total_net_delegated" >= 1000 THEN 'Tier1' WHEN "total_net_delegated" >= 100 and "total_net_delegated" < 1000 THEN 'Tier2' WHEN "total_net_delegated" >= 10 and "total_net_delegated" < 100 THEN 'Tier3' WHEN "total_net_delegated" >= 1 and "total_net_delegated" < 10 THEN 'Tier4' WHEN "total_net_delegated" >= 0.1 and "total_net_delegated" < 1 THEN 'Tier5' WHEN "total_net_delegated" >= 0.01 and "total_net_delegated" < 0.1 THEN 'Tier6' WHEN "total_net_delegated" >= 0.001 and "total_net_delegated" < 0.01 THEN 'Tier7' WHEN "total_net_delegated" >= 0.0001 and "total_net_delegated" < 0.001 THEN 'Tier8' WHEN "total_net_delegated" < 0.0001 THEN 'Tier9' END AS "tier", CASE WHEN "total_net_delegated" >= 1000 THEN '> 1000 $BGT' WHEN "total_net_delegated" >= 100 and "total_net_delegated" < 1000 THEN '100-1000 $BGT' WHEN "total_net_delegated" >= 10 and "total_net_delegated" < 100 THEN '10-100 $BGT' WHEN "total_net_delegated" >= 1 and "total_net_delegated" < 10 THEN '1-10 $BGT' WHEN "total_net_delegated" >= 0.1 and "total_net_delegated" < 1 THEN '0.1-1 $BGT' WHEN "total_net_delegated" >= 0.01 and "total_net_delegated" < 0.1 THEN '0.01-0.1 $BGT' WHEN "total_net_delegated" >= 0.001 and "total_net_delegated" < 0.01 THEN '0.001-0.01 $BGT' WHEN "total_net_delegated" >= 0.0001 and "total_net_delegated" < 0.001 THEN '0.0001-0.001 $BGT' WHEN "total_net_delegated" < 0.0001 THEN '< 0.0001 $BGT' END AS "description", COUNT(1) AS "total_wallets", SUM("total_net_delegated") AS "total_tier_delegation" FROM tiertbl GROUP BY 1,2 order by 1 asc

Berachain validator analysis