token_value_at_block.sql

CREATE OR REPLACE VIEW accounts.address_token_balances AS (
  SELECT
      address_hash,
      token_contract_address_hash,
      block_number,
      LEAD(block_number, 1, 2147483647) OVER (
          PARTITION BY address_hash, token_contract_address_hash
          ORDER BY block_number ASC
      ) as next_block_number,
      SUM(amount) OVER (
          PARTITION BY address_hash, token_contract_address_hash
          ORDER BY block_number ASC
          ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
      ) AS amount
      FROM (
          SELECT
              xfer.src AS address_hash,
              xfer.contract AS token_contract_address_hash,
              xfer.block_number AS block_number,
              -1 * xfer.amount AS amount
          FROM accounts.token_value_transfers AS xfer

          UNION ALL

          SELECT
              xfer.dst AS address_hash,
              xfer.contract AS token_contract_address_hash,
              xfer.block_number AS block_number,
              xfer.amount AS amount
          FROM accounts.token_value_transfers AS xfer
      ) as amount
);

create or replace function token_value_at_block(_token text, _address text, _block integer) returns numeric as
$$
  select amount
  from address_token_balances
  where
      address_hash = _address and
      token_contract_address_hash = _token and
      block_number <= _block
  order by block_number desc limit 1
$$ language sql stable returns null on null input;