Examples
ERC20 Token Balances for All Tokens in Wallet
Transfer(address indexed from, address indexed to, uint256 value)
select
max(block_num) block,
address token,
sum(
case
when "from" = 0xB9621a707869d45A600acc2851418a1fe60500e7
then -value
when "to" = 0xB9621a707869d45A600acc2851418a1fe60500e7
then value
else 0
end
) balance
from transfer
where (
"to" = 0xB9621a707869d45A600acc2851418a1fe60500e7
or "from" = 0xB9621a707869d45A600acc2851418a1fe60500e7
)
group by token
World App User Migration (case expressions)
Transfer(address indexed from, address indexed to, uint256 value)
SELECT
block_num,
COUNT (DISTINCT "to") AS migrated_users,
SUM (CASE
WHEN address = 0x2cfc85d8e48f8eab294be644d9e25c3030863003
THEN value
ELSE 0
END
) AS wld,
SUM (CASE
WHEN address = 0x03c7054bcb39f7b2e5b2c7acb37583e32d70cfa3
THEN value
ELSE 0
END
) AS btc,
SUM(CASE
WHEN address = 0x4200000000000000000000000000000000000006
THEN value
ELSE 0
END
) AS eth,
SUM (CASE
WHEN address = 0x79a02482a880bce3f13e09da970dc34db4cd24d1
THEN value
ELSE 0
END
) AS usdc
FROM transfer
WHERE "from" = 0xC6968c6DF1a2C31Ac66B42945BbaD91635a0095B
GROUP BY block_num
ORDER BY block_num DESC
LIMIT 20
Daimo Name Registrations
Registered(bytes32 indexed name, address indexed addr)
select
block_num,
h2s(name) name,
addr
from registered
where address = 0x4430A644B215a187a3daa5b114fA3f3d9DeBc17D
order by block_num desc
ERC-1155 Transfer Batch (arrays)
TransferBatch(address indexed operator, address indexed from, address indexed to, uint256[] ids, uint256[] amounts)
select
log_idx,
"from",
"to",
ids,
amounts
from transferbatch
where block_num = 21258465
and log_idx = 4
Unique Senders/Receivers of ERC-721 Transfers per Block
Transfer(address indexed from, address indexed to, uint indexed tokenId)
select
block_num,
count(distinct(tx_hash)) transactions,
count(distinct("from")) senders,
count(distinct("to")) receivers
from transfer
group by block_num
order by block_num desc
limit 1
ERC721 Current Token Holders (self join)
Transfer(address indexed from, address indexed to, uint indexed tokenId)
select t1."to", t1.tokenId, t1.block_num
from transfer t1
left join transfer t2
on t1.tokenId = t2.tokenId
and t1.block_num < t2.block_num
and t1.address = t2.address
where t1.address = 0xE81b94b09B9dE001b75f2133A0Fb37346f7E8BA4
and t2.tokenId is null
Base Name Registrations
NameRegistered(string name, bytes32 indexed label, address indexed owner, uint256 expires)
select
tx_hash,
name,
owner
from nameregistered
where address = 0x4ccb0bb02fcaba27e82a56646e81d8c5bc4119a5
order by block_num desc
limit 100
Zora Mint Comments from a Random User
MintComment(address indexed sender, address indexed collection, uint256 indexed tokenId, uint256 quantity, string comment)
select comment
from mintcomment
where sender = 0x1d14d9e297dfbce003f5a8ebcf8cba7faee70b91
History
This query is taking too much time.
Reasons Why
- The Event is common and has millions of rows. For instance:
TransferorSwap - The Event and Contract have millions of rows. For instance:
USDC -
The Event and Contract are common and the query has a predicate on a non-indexed field.
When an Event contains an indexed field (egFoo(address indexed bar, uint256 baz)) and there is a query`select baz from foo where bar = 0x... and baz > 42`the query planner will use a database index to find allFooEvents`where bar = 0x...`but if that set contains millions of rows then the database will have to scan each of them checking for`baz > 42`–which is expensive. - There may be a bug! Please email: support@indexsupply.com
Tips for Fast Queries
- Use
`where address = 0x000...000`when possible. If a particular event is popular (eg Transfer(...)) and if you are only interested in Transfers for a particular contract, then adding the address predicate will speed up the query. -
Use
`where block_num > X`or`where block_num >= X and block_num < Y`to paginate through a large dataset. In general, addingblock_numpredicates to your query will positively impact performance.
In fact, live queries are sub-millisecond becase they only query recent blocks. - Consider using events with indexed fields. Each indexed field in the event signature is indexed in the database. If you are designing a new contract, keep this in mind.
- Finally, if you need low latency access to non-indexed data, the Dedicated plan will allow you to create custom database indexes over non-indexed data resulting in sub-millisecond queries. Please create an account and reach out to support@indexsupply.com for assistance.