Schema

Column Name	Data Type
block_number	bigDecimal
txn_hash	string
block_timestamp	bigDecimal
token_address	string
token_name	string
token_symbol	string
token_decimals	bigDecimal
owner	string
spender	string
value_before	bigDecimal
value_after	bigDecimal

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