Analyze ERC-20 transfers for a UTC day

Fetch ERC-20 Transfer logs for one UTC day and query the decoded rows with SQL.

Goal

Fetch ERC-20 Transfer logs for one UTC day and query the decoded rows with SQL. The example uses PENDLE on Ethereum mainnet.

Before you begin

Use an Ethereum client that serves historical logs for the requested date.

Each cell includes a saved result. Select Run all to query the provider.

1. Resolve the UTC day

Use block_range to translate 2024-01-06 into a block interval. get_logs uses an inclusive upper bound, so the query subtracts one from the interval end.

1
WITH resolved AS (
2
SELECT block_range($client, '2024-01-06'::DATE) AS block_interval
3
)
4
SELECT
5
'2024-01-06'::DATE AS utc_day,
6
(block_interval).from_block AS from_block,
7
(block_interval).to_block AS to_block_exclusive,
8
(block_interval).to_block - 1 AS to_block_inclusive
9
FROM resolved;

2. Fetch and decode transfer logs

get_logs takes the token address, Transfer event signature, and block range. The query returns each matching log with decoded sender, recipient, and value fields.

1
WITH resolved AS (
2
SELECT block_range($client, '2024-01-06'::DATE) AS block_interval
3
),
4
bounds AS (
5
SELECT
6
(block_interval).from_block AS from_block,
7
(block_interval).to_block AS to_block_exclusive,
8
(block_interval).to_block - 1 AS to_block_inclusive
9
FROM resolved
10
)
11
SELECT
12
bounds.from_block AS scan_from_block,
13
bounds.to_block_exclusive AS scan_to_block_exclusive,
14
bounds.to_block_inclusive AS scan_to_block_inclusive,
15
logs.block_number,
16
logs.block_hash,
17
logs.transaction_index,
18
logs.transaction_hash,
19
logs.log_index,
20
logs."from" AS sender,
21
logs."to" AS recipient,
22
logs.value AS value_raw,
23
format_units(logs.value, 18) AS pendle_amount
24
FROM bounds
25
CROSS JOIN get_logs(
26
$client,
27
'0x808507121B80c02388fAd14726482e061B8da827'::ADDRESS,
28
'event Transfer(address indexed from, address indexed to, uint256 value)',
29
bounds.from_block,
30
bounds.to_block_inclusive
31
) AS logs
32
ORDER BY logs.block_number, logs.transaction_index, logs.log_index;

3. Aggregate top recipients

Group the decoded logs by recipient and rank the transferred value.

1
WITH resolved AS (
2
SELECT block_range($client, '2024-01-06'::DATE) AS block_interval
3
),
4
bounds AS (
5
SELECT
6
(block_interval).from_block AS from_block,
7
(block_interval).to_block - 1 AS to_block_inclusive
8
FROM resolved
9
),
10
transfers AS (
11
SELECT
12
logs."to" AS recipient,
13
logs.value AS value_raw
14
FROM bounds
15
CROSS JOIN get_logs(
16
$client,
17
'0x808507121B80c02388fAd14726482e061B8da827'::ADDRESS,
18
'event Transfer(address indexed from, address indexed to, uint256 value)',
19
bounds.from_block,
20
bounds.to_block_inclusive
21
) AS logs
22
)
23
SELECT
24
recipient,
25
count(*) AS recipient_transfer_count,
26
sum(value_raw) AS received_raw,
27
format_units(sum(value_raw), 18) AS pendle_received
28
FROM transfers
29
GROUP BY recipient
30
ORDER BY received_raw DESC, recipient
31
LIMIT 5;

Functions used in this guide