Changes in allowances in ERC20 contracts on Ethereum

Schema

Column NameData Type
block_numberbigDecimal
txn_hashstring
block_timestampbigDecimal
token_addressstring
token_namestring
token_symbolstring
token_decimalsbigDecimal
ownerstring
spenderstring
value_beforebigDecimal
value_afterbigDecimal

Source

This table was built in sim's Ethereum ERC20 Allowance Changes canvas.

Usage notes

It tracks values in the uint256 format of contract storage. For the presentation users expect, divide values here by 10 to the power of d, where d is the decimals() of the given contract.

Sample queries

1. Current allowances for a given address

WITH latest_global_counter AS (
  SELECT token_address,
    owner,
    spender,
    MAX(CAST(global_counter as double)) AS latest_global_counter
  FROM @sim.ethereum_erc20_allowance_changes
  WHERE owner = '0x48d004a6c175db331e99beaf64423b3098357ae7'
  GROUP BY token_address, owner, spender
)
SELECT b.token_address,
  b.owner,
  b.spender,
  b.value_after AS value,
  b.block_number AS block_number_last_changed
FROM @sim.ethereum_erc20_allowance_changes b
  INNER JOIN latest_global_counter lgc 
    ON b.token_address = lgc.token_address
    AND b.owner = lgc.owner
    AND b.spender = lgc.spender
    AND b.global_counter = lgc.latest_global_counter
WHERE b.owner = '0x48d004a6c175db331e99beaf64423b3098357ae7'

2. A history of allowances for a given (token, owner)-pair

SELECT *
FROM @sim.ethereum_erc20_allowance_changes
WHERE token_address = '0x4b520c812e8430659fc9f12f6d0c39026c83588d'
AND owner = '0x48d004a6c175db331e99beaf64423b3098357ae7'
ORDER BY global_counter desc

3. All current allowances for a given token

WITH latest_global_counter AS (
  SELECT token_address,
    owner,
    spender,
    MAX(CAST(global_counter as double)) AS latest_global_counter
  FROM @sim.ethereum_erc20_allowance_changes
  WHERE token_address = '0x4b520c812e8430659fc9f12f6d0c39026c83588d'
  GROUP BY token_address, owner, spender
)
SELECT b.token_address,
  b.owner,
  b.spender,
  b.value_after AS value,
  b.block_number AS block_number_last_changed
FROM @sim.ethereum_erc20_allowance_changes b
  INNER JOIN latest_global_counter lgc 
    ON b.token_address = lgc.token_address
    AND b.owner = lgc.owner
    AND b.spender = lgc.spender
    AND b.global_counter = lgc.latest_global_counter
WHERE b.token_address = '0x4b520c812e8430659fc9f12f6d0c39026c83588d'
AND value_after > 0
LIMIT 100 --remove if you want the full set