Analyze ERC-20 transfers for a UTC day

Goal

Fetch and decode one UTC day of ERC-20 Transfer logs, then rank recipients by the total amount received. The runnable example uses PENDLE on Ethereum mainnet, so the SQL uses its contract address and 18-decimal formatting.

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 get the block interval for 2024-01-06. The interval’s to_block is exclusive, but get_logs expects an inclusive upper bound. The query passes to_block - 1.

WITH resolved AS (
  SELECT block_range($client, '2024-01-06'::DATE) AS block_interval
)
SELECT
  '2024-01-06'::DATE AS utc_day,
  (block_interval).from_block AS from_block,
  (block_interval).to_block AS to_block_exclusive,
  (block_interval).to_block - 1 AS to_block_inclusive
FROM resolved;

Loading cached snapshot · 1 rows

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.

WITH resolved AS (
  SELECT block_range($client, '2024-01-06'::DATE) AS block_interval
),
bounds AS (
  SELECT
      (block_interval).from_block AS from_block,
      (block_interval).to_block AS to_block_exclusive,
      (block_interval).to_block - 1 AS to_block_inclusive
  FROM resolved
)
SELECT
  bounds.from_block AS scan_from_block,
  bounds.to_block_exclusive AS scan_to_block_exclusive,
  bounds.to_block_inclusive AS scan_to_block_inclusive,
  logs.block_number,
  logs.block_hash,
  logs.transaction_index,
  logs.transaction_hash,
  logs.log_index,
  logs."from" AS sender,
  logs."to" AS recipient,
  logs.value AS value_raw,
  format_units(logs.value, 18) AS pendle_amount
FROM bounds
CROSS JOIN get_logs(
  $client,
  '0x808507121B80c02388fAd14726482e061B8da827'::ADDRESS,
  'event Transfer(address indexed from, address indexed to, uint256 value)',
  bounds.from_block,
  bounds.to_block_inclusive
) AS logs
ORDER BY logs.block_number, logs.transaction_index, logs.log_index;

Loading cached snapshot · 507 rows

3. Aggregate top recipients

Count transfers and sum the amount received by each recipient. Return the five recipients with the largest totals.

WITH resolved AS (
  SELECT block_range($client, '2024-01-06'::DATE) AS block_interval
),
bounds AS (
  SELECT
      (block_interval).from_block AS from_block,
      (block_interval).to_block - 1 AS to_block_inclusive
  FROM resolved
),
transfers AS (
  SELECT
      logs."to" AS recipient,
      logs.value AS value_raw
  FROM bounds
  CROSS JOIN get_logs(
      $client,
      '0x808507121B80c02388fAd14726482e061B8da827'::ADDRESS,
      'event Transfer(address indexed from, address indexed to, uint256 value)',
      bounds.from_block,
      bounds.to_block_inclusive
  ) AS logs
)
SELECT
  recipient,
  count(*) AS recipient_transfer_count,
  sum(value_raw) AS received_raw,
  format_units(sum(value_raw), 18) AS pendle_received
FROM transfers
GROUP BY recipient
ORDER BY received_raw DESC, recipient
LIMIT 5;

Loading cached snapshot · 5 rows