The main query used in this report uses common table expressions (CTEs) to organize the data. The first CTE, arb_price
, retrieves the hourly price of the ARB token from the ethereum.core.fact_hourly_token_prices
table. The second CTE, delegate_actions
, retrieves the transaction hash of transactions sent to the address 0x912ce59144191c1204e64559fe8253a0e49e6548
from the arbitrum.core.fact_transactions
table. This is the main address for sending ARB tokens to delegate voting authority.
The third CTE, delegators
, retrieves the delegator address and the maximum block timestamp from the arbitrum.core.fact_event_logs
table where the transaction hash is in the delegate_actions
CTE and the event name is DelegateChanged
. The fourth CTE, toDelegates
, retrieves the delegator
and toDelegate
addresses from the arbitrum.core.fact_event_logs
table where the transaction hash is in the delegate_actions
CTE, the event name
is DelegateChanged,
and the delegator
is not null. This CTE also joins with the delegators
CTE on the maximum block timestamp
and delegator
address.
The fifth and sixth CTEs, transfer_date_arb
and transfer_data_usd
, retrieve data from the arbitrum.core.fact_token_transfers
table for inflows and outflows of the ARB token to and from wallets. The transfer_date_arb
CTE calculates the amount in ARB while the transfer_data_usd
CTE calculates the amount in USD by joining with the arb_price
CTE on the hourly block timestamp.
The seventh and eighth CTEs, arbitrum_balance_arb
and arbitrum_balance_usd
, calculate the balance of each wallet in ARB and USD currencies by summing up inflows and subtracting outflows from their respective transfer data CTEs.
The ninth CTE, results
, calculates various metrics such as sum, max, average delegated amount, number of delegators, and number of transactions for each toDelegate address in both ARB and USD currencies. It does this by joining with either the arbitrum_balance_arb
or arbitrum_balance_usd
CTE on wallet = delegator
where wallet is not null.
The final query calculates various metrics such as sum, max, average volume, number of delegates and delegators, volume per delegate and delegator, number of transactions, and volume per transaction by selecting from the results of the ninth CTE.
The point that should be emphasized in this query is that the number of Delegators is counted only when the balance of their delegated ARB tokens is more than zero and not null. Therefore, the number of delegators may be displayed less than the actual number. In this regard, it is described more in the Delegators Activities tab.