Average balances per account

query.sql

-- Average balances per account
WITH balances AS (
    SELECT abh.balance as balance,
            abh.account_id
    FROM account_resource_balance_history abh
    where  abh.resource_id = 1 AND abh.to_state_version is null
),
    staked_balances AS (
      SELECT
          account_id,
          validator_id,
          total_stake_units
     FROM account_validator_stake_history ash
         where  to_state_version is null
         order by from_state_version
),
validator_balances AS (
        select validator_id,
               total_stake_units,
               total_prepared_unstake_units,
               total_xrd_staked

        FROM validator_stake_history vsh
         where  vsh.to_state_version is null
         order by vsh.from_state_version
    ),
totals AS (
    SELECT AVG(balance / pow(10, 18)) as balance,
       SUM(CASE WHEN validator_balances.total_stake_units IS NULL OR validator_balances.total_stake_units IS NULL OR validator_balances.total_stake_units = 0
       THEN 0 ELSE
       (staked_balances.total_stake_units
            * validator_balances.total_xrd_staked
            / (validator_balances.total_stake_units + validator_balances.total_prepared_unstake_units)
            / pow(10, 18))
           END) as staked,
    balances.account_id
FROM balances
LEFT JOIN staked_balances ON (staked_balances.account_id = balances.account_id)
LEFT JOIN validator_balances ON (staked_balances.validator_id = validator_balances.validator_id)
GROUP BY balances.account_id
ORDER BY balances.account_id
)
SELECT AVG(balance + staked) FROM totals
WHERE (balance + staked) < 1000000
AND (balance + staked) >= 100