with queue as (select block_timestamp, tx_hash, data, '0x' || SUBSTR(topics[1]::STRING, 27) AS delegator, '0x' || SUBSTR(topics[2]::STRING, 27) AS validator_address, ltrim(data, '0x') as decoded, livequery.utils.udf_hex_to_int(decoded)/pow(10,18) as amount from berachain.testnet.fact_event_logs where TOPICS[0] = '0x110aaf2f67d6465fc043087acff06b302e8db1b6b9157d72ee4d4cc5c96bcb9d' and CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' and ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' and tx_hash not in (select tx_hash from berachain.testnet.fact_event_logs where origin_function_signature = '0xc2ca9c3a') ) , undelegate as (select block_timestamp, tx_hash, data, '0x' || SUBSTR(topics[1]::STRING, 27) AS undelegator, '0x' || SUBSTR(topics[2]::STRING, 27) AS validator_address, ltrim(data, '0x') as decoded, livequery.utils.udf_hex_to_int(decoded)/pow(10,18) as amount from berachain.testnet.fact_event_logs where TOPICS[0] = '0xdc232a1d360a44eb299ff026b5f6badfe40d17f95b96da5db7168e88662e9a2c' and CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' and ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad') , active as (select block_timestamp, tx_hash, data, '0x' || SUBSTR(topics[1]::STRING, 27) AS delegator, '0x' || SUBSTR(topics[2]::STRING, 27) AS validator_address, ltrim(data, '0x') as decoded, livequery.utils.udf_hex_to_int(decoded)/pow(10,18) as amount from berachain.testnet.fact_event_logs where topics[0] = '0x99966631dd6d6c02c5416ca2369709e025ff974a2f1b3f11c8b74acc67731f0e' and CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' and ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' UNION all select block_timestamp, tx_hash, data, origin_from_address as delegator, '0x2d764dfeaac00390c69985631aaa7cc3fcfafaff' as validator_address, null as decoded, livequery.utils.udf_hex_to_int(data)/pow(10,18)*0.898 as amount from berachain.testnet.fact_event_logs where substr(topics[1],27) = '0000000000000000000000000000000000000000' and substr(topics[2],27) = 'e41779952f5485db5440452dfa43350556aa4673' and topics[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' and contract_address = '0x46efc86f0d7455f135cc9df501673739d513e982' and tx_succeeded = 'TRUE' ) , cancel as (select block_timestamp, tx_hash, data, '0x' || SUBSTR(topics[1]::STRING, 27) AS delegator, '0x' || SUBSTR(topics[2]::STRING, 27) AS validator_address, ltrim(data, '0x') as decoded, livequery.utils.udf_hex_to_int(decoded)/pow(10,18) as amount from berachain.testnet.fact_event_logs where topics[0] = '0x4556e704857739059d8dd18ea57df12e3b2b0d29fda4d46d19a113ebce73d1ec' and CONTRACT_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' and ORIGIN_TO_ADDRESS = '0xbda130737bdd9618301681329bf2e46a016ff9ad' and ORIGIN_FUNCTION_SIGNATURE = '0xc2ca9c3a' ) , active_val as ( select delegator, sum(amount) as delegated_amount, count(DISTINCT tx_hash) as delegate_tx, count(DISTINCT validator_address) as validators from active group by 1) select count(DISTINCT delegator) as users, case when delegated_amount <= 1 then 'a. <= 1 BGT' when delegated_amount <= 5 then 'b. 1-5 BGT' when delegated_amount <= 10 then 'c. 5-10 BGT' when delegated_amount <= 15 then 'd. 10-15 BGT' when delegated_amount <= 25 then 'e. 15-25 BGT' when delegated_amount <= 50 then 'f. 25-50 BGT' when delegated_amount <= 75 then 'g. 50-75 BGT' when delegated_amount <= 100 then 'h. 75-100 BGT' when delegated_amount <= 500 then 'i. 100-500 BGT' when delegated_amount > 500 then 'j. > 500 BGT' end as breakdown from active_val group by 2 order by 2 asc

State of the Berachain Testnet