# Analyze ERC-20 transfers for a UTC day

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

Canonical HTML: <https://determica.com/docs/guides/analyze-erc20-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.

## Parameters

- **Ethereum client** (`client`)

### 1. Resolve the UTC day

Use [`block_range`](/docs/functions/block_range.md) to get the block interval for
`2024-01-06`. The interval's `to_block` is exclusive, but
[`get_logs`](/docs/functions/get_logs.md) expects an inclusive upper bound. The
query passes `to_block - 1`.

#### Resolve the day to blocks

Resolve one UTC date to a start block, an exclusive end block, and an inclusive end block.

```sql
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;
```

### 2. Fetch and decode transfer logs

[`get_logs`](/docs/functions/get_logs.md) takes the token address, `Transfer` event
signature, and block range. The query returns each matching log with decoded
sender, recipient, and value fields.

#### Fetch ERC-20 Transfer logs

Read and decode ERC-20 Transfer logs for one UTC day.

```sql
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;
```

### 3. Aggregate top recipients

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

#### Rank recipients by amount received

Count transfers and sum the amount received by each recipient.

```sql
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;
```

## Functions used in this guide

- [block_range](/docs/functions/block_range.md)
- [get_logs](/docs/functions/get_logs.md)
- [format_units](/docs/functions/format_units.md)

## Continue with

- [get_transaction_logs](/docs/functions/get_transaction_logs.md)
- [event_decode_json](/docs/functions/event_decode_json.md)
- [event_signature](/docs/functions/event_signature.md)