The page is for demonstration of our work of Encode Hackathon 2024. This is the data analysis challenge from https://www.compasslabs.ai/.

‣

🔮 Zuyue Jiang [email protected] 🔮 Grace Chen [email protected] 🔮 Yusi Lei [email protected]

🎀 Introduction

In the world of decentralized finance (DeFi), understanding the behavior of smart contracts is essential for assessing market dynamics, liquidity, and user engagement. In this project, we did an investigation on Ethereum smart contract event data to explore patterns and trends. It provides a foundation for deeper investigations into Ethereum’s DeFi ecosystem. Hope this project could help users to make more reliable decisions and inspire developers to improve protocol design and transaction efficiency.

🎀 Data and Methods

• Source of Data: The dataset is a subset of event and call data captured from Ethereum smart contracts, including decoded, human-readable data. It is extracted from the hackathon_ethereum_events table, where the full process is provided in Compass Labs’ document.
• Data Processing: The data is provided by Compass Labs and is hosted on Google Cloud’s SQL service. We used Postsql to extract the data and connected it to Looker Studio, where we did most of the analyze and visualization for insights into contract behavior, volume trends, and gas costs.

🎀 Data Exploration

• check heading 10 rows

  select * from hackathon_ethereum_events
  limit 10
  

  • first 10 rows
  • contract: Ethereum address of the contract emitting the event.
  • block: Block number where the event was recorded.
  • event_name: Type of event emitted (e.g., Borrow, Swap).
  • event_data: JSON-formatted details specific to the event type (e.g., sender, amount).
  • transaction_hash: Unique identifier of the transaction.
  • gas: Gas units consumed for this transaction.
  • gas_price: Price per gas unit in wei for the transaction.
  • log_index: Index of the log entry within the block.
  • block_timestamp: Date and time when the block was mined.

• find unique event_name and check each pattern of event_data

  SELECT DISTINCT event_name 
  FROM hackathon_ethereum_events
  

  • event_name

  SELECT DISTINCT ON (event_name) event_name, event_data FROM hackathon_ethereum_events
  ORDER BY event_name, random();
  

  • event_data
  • check detailed explanation from https://docs.uniswap.org/contracts/v3/reference/core/interfaces/pool/IUniswapV3PoolEvents.

• all columns pattern check

  SELECT DISTINCT ON (event_name) * FROM hackathon_ethereum_events
  ORDER BY event_name, random();
  

  • one row for each event_name

🎀 General Analysis

I. Event Distribution

Here we use box plot and time-series line plot to analyze the distribution and trend of various events (such as Swap, Supply, Withdraw, and ReserveDataUpdated). We highlighted the most frequent interactions within DeFi contracts. Tracking these events over time shows how user behavior or protocol updates influence activity levels. Investigating the event distribution also provides insights into which events drive the most significant interactions.

https://lookerstudio.google.com/embed/reporting/bc5553c4-f4d1-4885-a9ae-2260b34bcab0/page/jhIHE

Observations & Suggestions

Common events include: