Flipside Docs
Search…
Getting Started with Ethereum Events
This guide will take you thru an introduction of the Ethereum events table.
This guide provides an introduction to the ethereum.udm_events table via a series of simple queries that explore the data.
For a breakdown of the Ethereum Events table schema go here.
Let's familiarize ourselves with the table by first looking at the types of events that can be queried.
1
SELECT distinct(event_type) FROM ethereum.udm_events
Copied!
event_type
description
function
A function call
event
An event emitted from a function call.
erc20_transfer
An event involving a transfer of an ERC20 token.
native_eth
A native eth transfer.
This tells us there are 4 types of events that get recorded in the ethereum events table. Let's take a closer look at the decoded on-chain event names for USDC over the past 30 days.
1
SELECT
2
event_type,
3
event_name as event_name,
4
count(event_name)
5
FROM ethereum.udm_events
6
WHERE
7
-- USDC contract address
8
contract_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' AND
9
block_timestamp >= GETDATE() - interval'30 days'
10
GROUP BY 1,2
11
ORDER BY 3 DESC
Copied!
event_type
event_name
count
erc20_transfer
transfer
1,142,589
event
Approval
167,014
event
Mint
525
event
Burn
514
event
MinterConfigured
15
These results provide a more granular breakdown of the events surrounding the USDC contract. Specifically, this shows us the number of Approval, Mint, Burn, and MinterConfigured events that were emitted by the USDC contract. It appears that the greatest number of events are related to transfers of the USDC token. In our next query let's look at the trend of USDC transfers of the past 30 days.
1
SELECT
2
date_trunc('day', block_timestamp) as metric_date,
3
sum(amount) as total_amount
4
FROM ethereum.udm_events
5
WHERE
6
event_type = 'erc20_transfer' AND
7
symbol = 'USDC' AND
8
amount > 0 AND
9
block_timestamp >= GETDATE() - interval'30 days'
10
GROUP BY metric_date
11
ORDER BY metric_date DESC
Copied!
We see a big spike in USDC transfers on October 26th. This just happens to coincide with the Harvest.finance attack on the same date. Let's take a closer look at the exchanges that USDC was being sent to at that time by leveraging Flipside's Exchange labels.
1
SELECT
2
to_label,
3
sum(amount) as total_amount
4
FROM ethereum.udm_events
5
WHERE
6
-- the 'distributor' label type = exchanges
7
to_label_type = 'distributor' AND
8
event_type = 'erc20_transfer' AND
9
symbol = 'USDC' AND
10
amount > 0 AND
11
block_timestamp >= '2020-10-26T00:00:00Z' AND
12
block_timestamp <= '2020-10-27T00:00:00Z'
13
GROUP BY to_label, to_label_type
14
ORDER BY total_amount DESC
15
LIMIT 5
Copied!
to_label
total_amount
curve fi
2,786,908,380.42
yearn
1,431,392,778.53
ftx exchange
168,161,544.95
binance
82,280,347.55
uniswap
44,740,268.63
Here we see that Curve saw the largest influx at ~2.7B, followed closely by Yearn.
From here I encourage you to dig deeper by exploring the inflows and outflows on each of these exchanges, perhaps even looking at individual pool activity on curve or uniswap by leveraging the project labels.
Copy link