sum(amount) AS amount -- Net inflow or outflow per day
SELECT date_trunc('day', evt_block_time) AS day,
tr.contract_address AS token_address,
FROM erc20."ERC20_evt_Transfer" tr
WHERE "to" = '\x70c730465dff5447a12bae37090446745c9edccc' --Filter for holding address
-- AND contract_address = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- Filter for token address if you only want to see a specific token
SELECT date_trunc('day', evt_block_time) AS day,
tr.contract_address AS token_address,
FROM erc20."ERC20_evt_Transfer" tr
WHERE "from" = '\x70c730465dff5447a12bae37090446745c9edccc' --Filter for holding address
-- AND contract_address = '\xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- Filter for token address if you only want to see a specific token
, balances_with_gap_days AS (
SUM(amount) OVER (PARTITION BY token_address, address ORDER BY t.day) AS balance, -- balance per day with a transfer
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
SELECT generate_series('2020-07-01'::timestamp, date_trunc('day', NOW()), '1 day') AS day -- Generate all days since the first contract
SUM(balance/10^decimals) AS balance
FROM balances_with_gap_days b
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
INNER JOIN erc20.tokens erc ON b.token_address = erc.contract_address
SUM(balance) AS token_balance,
SUM(balance*p.price) AS balance_usd_value
SELECT date_trunc('day', p.minute) as day,
) p ON b.day = p.day AND b.token_address = p.contract_address