-- Parameters to alter:
-- [1] wallet_addresses: list of wallet addresses, comma-separated
-- e.g: 0x915867061ea708869e34819216ae726041f48739, 0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037
-- [2] token_address: token contract of interest, comma-separated
-- e.g: 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 0xdac17f958d2ee523a2206206994597c13d831ec7
-- [3] time_granularity: time granularity of address: e.g. hour / day / month
-- [4] blockchain: blockchain of interest: e.g. ethereum
-- [5] balances_table: name of balances table: e.g. erc20_balances
WITH
wallets AS (
SELECT DISTINCT value::varchar AS wallet_address
FROM
(SELECT split(replace('{{wallet_addresses}}', ' ', ''), ',') AS addresses),
LATERAL FLATTEN(input => addresses)
),
tokens AS (
SELECT DISTINCT value::varchar AS token_address
FROM
(SELECT split(replace('{{token_address}}', ' ', ''), ',') AS addresses),
LATERAL FLATTEN(input => addresses)
),
balances AS (
SELECT
date_trunc('{{time_granularity}}', b.block_timestamp) AS date,
b.block_number,
b.address,
b.raw_balance,
b.usd_balance,
b.balance,
b.token_address,
b.token_name,
b.token_symbol
FROM {{blockchain}}.assets.{{balances_table}} b
INNER JOIN wallets ON b.address = wallets.wallet_address
INNER JOIN tokens ON b.token_address = tokens.token_address
QUALIFY
row_number() OVER (
PARTITION BY b.address, b.token_address, date ORDER BY b.block_number DESC
) = 1
),
distinct_address_tokens AS ( -- Generate a list of address x token
SELECT DISTINCT address, token_address FROM balances
),
distinct_dates AS ( -- Generate a list of timestamps
SELECT date_trunc('{{time_granularity}}', timestamp) AS date, count(1) AS count_all
FROM {{blockchain}}.raw.blocks
WHERE timestamp >= (SELECT min(date) FROM balances)
GROUP BY date
),
date_address_token_cte AS ( -- Generate timestamp x address x tokens
SELECT t1.date, t2.address, t2.token_address
FROM distinct_dates t1, distinct_address_tokens t2
),
final AS (
SELECT
t1.date,
t1.address,
lag(t2.token_address) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS token_address,
lag(t2.token_name) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS token_name,
lag(t2.token_symbol) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS token_symbol,
lag(t2.usd_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS usd_balance,
lag(t2.raw_balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS raw_balance,
lag(t2.balance) ignore nulls over (partition by t1.address, t1.token_address order by t1.date) AS balance
FROM date_address_token_cte t1
LEFT JOIN balances t2
ON t1.date = t2.date
AND t1.address = t2.address
AND t1.token_address = t2.token_address
WHERE 1 = 1
)
SELECT
date,
token_address,
token_name,
token_symbol,
usd_balance,
raw_balance,
balance
FROM final
WHERE raw_balance > 0
ORDER BY date DESC
