WITH
address_table AS (
SELECT DISTINCT
CAST(value AS VARCHAR) AS address_filter
FROM
(
SELECT
SPLIT(
REPLACE(
'0x915867061ea708869e34819216ae726041f48739, 0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037',
' ',
''
),
','
) AS addresses
),
LATERAL FLATTEN(input => addresses)
),
tokens_table AS (
SELECT DISTINCT
CAST(value AS VARCHAR) AS tokens_filter
FROM
(
SELECT
SPLIT(
REPLACE(
'0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48, 0xdac17f958d2ee523a2206206994597c13d831ec7',
' ',
''
),
','
) AS addresses
),
LATERAL FLATTEN(input => addresses)
),
daily_balances AS (
SELECT
DATE_TRUNC('day', balances.block_timestamp) AS date,
balances.block_number,
balances.address,
balances.raw_balance,
balances.usd_balance,
balances.balance,
balances.token_address,
balances.token_name,
balances.token_symbol
FROM
ethereum.assets.erc20_balances AS balances
INNER JOIN address_table ON address = address_filter
INNER JOIN tokens_table ON token_address = tokens_filter
QUALIFY
ROW_NUMBER() OVER (
PARTITION BY
address,
token_address,
date
ORDER BY
block_number DESC
) = 1
),
hoders_dates AS (
SELECT
DATE_TRUNC('day', timestamp) AS date,
address,
token_address,
COUNT(1) AS count_all
FROM
ethereum.raw.blocks,
daily_balances
WHERE
timestamp >= (
SELECT
MIN(date)
FROM
daily_balances
)
GROUP BY
ALL
),
final_balances 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
hoders_dates AS t1
LEFT JOIN daily_balances AS t2 ON t1.date = t2.date
AND t1.address = t2.address
AND t1.token_address = t2.token_address
WHERE
1 = 1
)
SELECT
*
FROM
final_balances
WHERE
raw_balance > 0
ORDER BY
1 DESC
LIMIT
20000
2024-07-01T00:00:00
0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037
2024-07-01T00:00:00
0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037
2024-07-01T00:00:00
0x915867061ea708869e34819216ae726041f48739
2024-07-01T00:00:00
0x915867061ea708869e34819216ae726041f48739
