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 5mo ago
Copy link