USD price for a token from Uniswap
The most common and easiest way to get token USD prices on Dune Analytics is with the prices.usd table. However, this data is fetched from centralised exchanges so for a long tail of tokens the best approach is to get prices from Uniswap.
This query uses WETH pairs, which is used to map to USD price. The query can be modified to work with any token that has a price in prices.usd
You can find this query on Dune here.
1
WITH weth_pairs AS ( -- Get exchange contract address and "other token" for WETH
2
SELECT cr."pair" AS contract,
3
CASE WHEN cr."token0" = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then '0' ELSE '1' END AS eth_token,
4
CASE WHEN cr."token1" = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' then cr."token0" ELSE cr."token1" END AS other_token
5
FROM uniswap_v2."Factory_evt_PairCreated" cr
6
WHERE token0 = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' OR token1 = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
7
)
8
9
, swap AS ( -- Get all trades on the pair last 14 days
10
SELECT
11
CASE WHEN eth_token = '0' then sw."amount0In" + sw."amount0Out" ELSE sw."amount1In" + sw."amount1Out"
12
END/1e18 AS eth_amt,
13
CASE WHEN eth_token = '1' then sw."amount0In" + sw."amount0Out" ELSE sw."amount1In" + sw."amount1Out"
14
END/power(10, tok."decimals") AS other_amt, -- If the token is not in the erc20.tokens list you can manually divide by 10^decimals
15
tok."symbol",
16
tok."contract_address",
17
date_trunc('hour', sw."evt_block_time") AS hour
18
FROM uniswap_v2."Pair_evt_Swap" sw
19
JOIN weth_pairs ON sw."contract_address" = weth_pairs."contract"
20
JOIN erc20."tokens" tok ON weth_pairs."other_token" = tok."contract_address"
21
WHERE other_token = '\xeb4c2781e4eba804ce9a9803c67d0893436bb27d' --renBTC example
22
-- To allow users to submit token address in the Dune UI you can use the below line:
23
-- WHERE other_token = CONCAT('\x', substring('{{Token address}}' from 3))::bytea -- Allow user to input 0x... format and convert to \x... format
24
AND sw.evt_block_time >= now() - interval '14 days'
25
)
26
27
, eth_prcs AS (
28
SELECT avg(price) eth_prc, date_trunc('hour', minute) AS hour
29
FROM prices.layer1_usd_eth
30
WHERE minute >= now() - interval '14 days'
31
group by 2
32
)
33
34
SELECT
35
AVG((eth_amt/other_amt)*eth_prc) AS usd_price,
36
swap."symbol" AS symbol,
37
swap."contract_address" AS contract_address,
38
eth_prcs."hour" AS hour
39
FROM swap JOIN eth_prcs ON swap."hour" = eth_prcs."hour"
40
GROUP BY 2,3,4
41
;
Copied!
Last modified 2mo ago
Copy link