Published
owned this note
owned this note
Linked with GitHub
# How to get started with Dune Analytics
Dune Analytics allows you to query human readable Ethereum data with SQL. You can then visualise your findings and share it with the world. Here are some tips and tricks on how to get started with our data and interface. Create a user for free at [duneanalytics.com](https://www.duneanalytics.com/).
## Table of contents
[TOC]
## Data tables
You can currently query from these databases:
1. **Ethereum mainnet**
* [Raw Ethereum data](#raw-ethereum-data)
* [Decoded smart contract data](#decoded-data)
* [Centralised exchanges trading data](#Centralised-exchanges-trading-data)
2. **Binance DEX (beta)**
### Ethereum Mainnet
#### Raw Ethereum data
* Blocks
* Logs
* Transactions
* Receipts
* Traces
You probably won't use this too much when doing analysis with Dune (see [decoded data](#decoded-data)), but it's always nice to have just in case.
You can [click here](https://ethereum.stackexchange.com/questions/268/ethereum-block-architecture) to learn more about Ethereum's data structure, but again it's not really needed for using Dune.
#### Decoded smart contract data
Instead of working with the traces, logs and receipts we transform smart contract activity to nice human readable type tables.
You will find a table for each `event` and `call` to and from a smart contract.
These are named as `projectName.contractName_evt_eventName` for events and `projectName.contractName_call_eventName` for calls.
For example `uniswap.Exchange_evt_AddLiquidity` or `uniswap.Exchange_call_addLiquidity`
Using the event tables is usually sufficient, but in some cases you will want to use the `call` tables. For instance Maker DAO which don't give you too many events you can use tables like `maker.SaiTub_call_draw`.
We have decoded data for the most popular smart contract projects. Let us know [here](https://airtable.com/shrGJczEfUx7ZIzs5) if you have a request for decoding of data.
#### Centralised exchanges trading data
Token volume is great, but more often than not you want to know the USD value of smart contract activity.
You can easily get and join that information with onchain data using the data we pull from the coincap API. See how to use it below.
* Coincap.symbols
* asset (address)
* symbol
* Coincap.tokens/usd
* asset (address)
* average
* time
* symbol (ticker)
Price is the volume-weighted price based on real-time market data every minute, translated to USD.
The data is fetched from this [Coincap API](https://docs.coincap.io/?0=o&1=n&2=b&3=o&4=a&5=r&6=d&7=i&8=n&9=g&10=c&11=o&12=m&13=p&14=l&15=e&16=t&17=e&version=latest#61e708a8-8876-4fb2-a418-86f12f308978).
* coincap.trading_summary
* volume_usd_24hour
* time
* symbol
* marketcap_usd
* asset
The data is fetched from this [Coincap API](https://docs.coincap.io/?0=o&1=n&2=b&3=o&4=a&5=r&6=d&7=i&8=n&9=g&10=c&11=o&12=m&13=p&14=l&15=e&16=t&17=e&version=latest#f8869879-171f-4240-adfd-dd2947506adc).
## How to work with the data
You can interact with the [data tables](#Data-tables) through our interface at [duneanalytics.com](https://www.duneanalytics.com/).
To create a new query you simply click Create -> Query
On your left you can select which database you want to use in the dropdown list and then see the data tables in the window. Just search for the project you are interested in working with.
### Tips you might find handy
#### Remove decimals
Ether transfers and most ERC-20 tokens have 18 decimal places. To get a more intuitive number you typically want to divide amounts and value transfers by `1e18`.
#### Using Inline Ethereum addresses
In Dune Ethereum addresses are stored as postgres bytearrays which are encoded with the `\x` prefix. This differs from the customary `0x` prefix. If you'd like to use an inline address, say to filter for a given token, you would do
```
WHERE token = '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359'
```
which is simply short for
```
WHERE token = '\x89d24a6b4ccb1b6faa2625fe562bdd9a23260359'::bytea
```
#### Join with transactions table to get time
If you want to get a time dimension for your query you can join an event or call table with `ethereum.transactions` on `tx_hash`.
We've added `block_time` to the `ethereum.transactions` table for your convenience. A neat way to use it is with the `date_trunc` function like this `SELECT date_trunc('week', tx.block_time) as time` Here you can use minute, day, week, month.
#### How to get USD price
To get the USD volume of onchain activity you typically want to join the smart contract event you are looking at with the usd price and join on minute.
`JOIN coincap."tokens/usd" p ON date_trunc('minute', p.time) = date_trunc('minute', tx.block_time)`
and make sure that asset matches asset
`AND b."asset" = p.asset`
Then you can simply multiply the value or amount from the smart contract event with the usd price in your `SELECT` statement: `* p.average`.
#### Token symbols
You often want to group your results by token address. For instance you want to see volume on a DEX grouped by token. However, a big list of token addresses are abstract and hard to digest. Therefore you often want to use the token symbol instead. Simply join the table `coincap.symbols` with your event table where asset = token address. You then select symbol in your select statement instead of token address.
**NB** The coincap table cointains tokens that are traded on centralised exchanges. If you are working with more obscure tokens you should be careful with joining with this table because tokens that are not in the coincap table might be excluded from your results.
## Visualize and share your findings
When you think your query is correct and are ready to give your results a beautiful visual graph you simply click *+ New Visualization* next to your results table.
Most often you want the Visualization Type to be either a Chart (for values over time and/or grouped by something) or a Counter (with a single important metric).
## Sample queries
### Retention
```sql
WITH new_user_activity AS
(SELECT activity.*
FROM
(SELECT src AS address,
date_trunc('week', tx.block_time) AS date
FROM maker."DAI_evt_Transfer" t
INNER JOIN ethereum.transactions tx ON t.tx_hash = tx.hash
WHERE tx.block_time > date_trunc('week', current_date)::date - 35
AND tx.block_time < date_trunc('week', current_date)::date
) AS activity
JOIN
(SELECT address,
MIN(date) AS first_time
FROM
(SELECT src AS address,
date_trunc('day', tx.block_time) AS date
FROM maker."DAI_evt_Transfer" t
INNER JOIN ethereum.transactions tx ON t.tx_hash = tx.hash) AS senders
GROUP BY address) AS users ON users.address = activity.address
AND users.first_time = activity.date),
cohort_active_user_count AS (
SELECT
date, count(distinct address) AS count
FROM new_user_activity
GROUP BY 1
)
SELECT date, period, new_users, retained_users, retention
FROM (
SELECT
new_user_activity.date AS date,
extract('epoch' from (future_activity.date - new_user_activity.date)) / 604800 AS period,
max(cohort_size.count) as new_users, -- all equal in group
count(distinct future_activity.address) AS retained_users,
count(distinct future_activity.address) /
max(cohort_size.count)::float as retention
FROM new_user_activity
LEFT JOIN
(SELECT src AS address,
date_trunc('week', tx.block_time) AS date
FROM maker."DAI_evt_Transfer" t
INNER JOIN ethereum.transactions tx ON t.tx_hash = tx.hash
WHERE tx.block_time > date_trunc('week', current_date)::date - 35
AND tx.block_time < date_trunc('week', current_date)::date) as future_activity on
new_user_activity.address = future_activity.address
AND new_user_activity.date < future_activity.date
AND (new_user_activity.date + interval '5 weeks')
>= future_activity.date
LEFT JOIN cohort_active_user_count as cohort_size on
new_user_activity.date = cohort_size.date
GROUP BY 1, 2) t
WHERE period is NOT NULL
ORDER BY date, period
```
## Working with specific projects
### Uniswap
When working with Uniswap data you typically want to know which token each exchange address belongs to. For the event `uniswap.Exchange_evt_EthPurchase` the field `address` tells you what exchange address the EthPurchase happened on. You can then join with the event `uniswap.Factory_evt_NewExchange` on the `exchange` field to see which token that exchange belongs to.
See [token symbols](#token-symbols) above on how to turn token addresses into symbols in your results.
### Compound v2
There are no events emitted when new token contracts are deployed with Compound v2. The Compound v2 markets therefore need to be manually mapped to different token symbols.
The easiest way to work with the Compound v2 data is to insert the relevant token information into your query with this snippet:
```
WITH temp_token_info (address, symbol, decimals, underlying_symbol, underlying_decimals) AS
(VALUES
('\x6c8c6b02e7b2be14d4fa6022dfd6d75921d90e4e', 'cBAT', 8, 'BAT', 18),
('\xf5dce57282a584d2746faf1593d3121fcac444dc', 'cDAI', 8, 'DAI', 18),
('\x4ddc2d193948926d02f9b1fe9e1daa0718270ed5', 'cETH', 8, 'ETH', 18),
('\x158079ee67fce2f58472a96584a73c7ab9ac95c1', 'cREP', 8, 'REP', 18),
('\x39aa39c021dfbae8fac545936693ac917d5e7563', 'cUSDC', 8, 'USDC', 6),
('\xb3319f5d18bc0d84dd1b4825dcde5d5f7266d407', 'cZRX', 8, 'ZRX', 18)
)
```
Then you can join address from `temp_token_info` with address from the event `compoundv2."cEther_evt_Borrow"` for instance. Thanks to Dune user pyggie for inspiring this snippet.
## Explore community analysis
You can explore what analysis other community members have created with Dune. Simply go to queries or dashboards in the top left of the Dune Analytics interface.
If you are interested in a specific project (for instance 0x or Augur) or a domain (for instance DeFi or gaming) simply search for it in the top tight corner and you'll only see results related to your search term.
If there are no results you might need to get your hands dirty and create the analysis yourself :)
## (TO-DO) List of decoded Ethereum smart contracts
