English
About Dune
Data Tables
Dune Engine V2 (beta)
Onboarding
Bounties
Dune Charts across the web
Resources
FAQ
Changelog
Token (and USD value) per token over time for an address
Note that this query can get very heavy when there are many tokens and transfers over a long period of time.
1
WITH transfers AS (
2
3
SELECT day,
4
address,
5
token_address,
6
sum(amount) AS amount -- Net inflow or outflow per day
7
FROM
8
9
(
10
SELECT date_trunc('day', evt_block_time) AS day,
11
"to" AS address,
12
tr.contract_address AS token_address,
13
value AS amount
14
FROM erc20."ERC20_evt_Transfer" tr
15
WHERE "to" = '\x70c730465dff5447a12bae37090446745c9edccc' --Filter for holding address
16
-- AND contract_address = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- Filter for token address if you only want to see a specific token
17
18
UNION ALL
19
20
SELECT date_trunc('day', evt_block_time) AS day,
21
"from" AS address,
22
tr.contract_address AS token_address,
23
-value AS amount
24
FROM erc20."ERC20_evt_Transfer" tr
25
WHERE "from" = '\x70c730465dff5447a12bae37090446745c9edccc' --Filter for holding address
26
-- AND contract_address = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- Filter for token address if you only want to see a specific token
27
) t
28
GROUP BY 1, 2, 3
29
)
30
​
31
, balances_with_gap_days AS (
32
SELECT t.day,
33
address,
34
t.token_address,
35
SUM(amount) OVER (PARTITION BY token_address, address ORDER BY t.day) AS balance, -- balance per day with a transfer
36
lead(day, 1, now()) OVER (PARTITION BY token_address, address ORDER BY t.day) AS next_day -- the day after a day with a transfer
37
FROM transfers t
38
)
39
40
, days AS (
41
SELECT generate_series('2020-07-01'::timestamp, date_trunc('day', NOW()), '1 day') AS day -- Generate all days since the first contract
42
)
43
44
, balance_all_days AS (
45
SELECT d.day,
46
address,
47
erc.symbol,
48
b.token_address,
49
SUM(balance/10^decimals) AS balance
50
FROM balances_with_gap_days b
51
INNER JOIN days d ON b.day <= d.day AND d.day < b.next_day -- Yields an observation for every day after the first transfer until the next day with transfer
52
INNER JOIN erc20.tokens erc ON b.token_address = erc.contract_address
53
GROUP BY 1, 2, 3, 4
54
ORDER BY 1, 2, 3, 4
55
)
56
​
57
SELECT b.day,
58
b.symbol,
59
b.token_address,
60
SUM(balance) AS token_balance,
61
SUM(balance*p.price) AS balance_usd_value
62
FROM balance_all_days b
63
LEFT JOIN (
64
SELECT date_trunc('day', p.minute) as day,
65
contract_address,
66
symbol,
67
decimals,
68
AVG(p.price) as price
69
FROM prices."usd" p
70
GROUP BY 1, 2, 3, 4
71
) p ON b.day = p.day AND b.token_address = p.contract_address
72
GROUP BY 1,2,3
73
ORDER BY 1,2,3
74
;
75
​
Copied!
Last modified 1yr ago
Copy link