Doing science...
Go to App
Balances View
Wallet Balances with daily granularity
Feb 2023
Mar 2023
Apr 2023
May 2023
Jun 2023
Jul 2023
Aug 2023
Sep 2023
Oct 2023
Nov 2023
Dec 2023
Jan 2024
Feb 2024
Mar 2024
Apr 2024
May 2024
Jun 2024
Jul 2024
date
5M
10M
15M
20M
25M
30M
35M
40M
45M
50M
55M
balance
token_address
0xdac17f958d2ee523a2206206994597c13d831ec7
0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48
Last updated 5 months ago
/* 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
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
View Full SQL
date
address
2024-07-01T00:00:00
0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037
2024-07-01T00:00:00
0x0d37eb1528e7313a3954f25d4d4aead0dc7ff037
2024-07-01T00:00:00
0x915867061ea708869e34819216ae726041f48739
2024-07-01T00:00:00
0x915867061ea708869e34819216ae726041f48739
