mondovReserve copy
Updated 2023-08-30
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from Abbas_ra21 / Reserve @ https://flipsidecrypto.xyz/Abbas_ra21/q/_3_4Uud0tLmI/reserve
-- some parts of the code is from sam message in discord:
-- https://discord.com/channels/784442203187314689/908794517795250196/1004329999643451472
with sam_address as (
select
t.*,
f.index,
f.value:pubkey as address
from
solana.core.fact_transactions t,
table(flatten(account_keys)) f
where
BLOCK_TIMESTAMP::date >= dateadd('Month',-1,current_Date)
)
select
date_trunc('Day', block_timestamp) AS Day,
avg(post_balances[index] / 1e9) AS Balance
from
sam_address
inner join (
select
date_trunc('Day', block_timestamp) AS Date,
max(block_timestamp) AS max
from
sam_address where
address = '3rBnnH9TTgd3xwu48rnzGsaQkSr1hR64nY71DrDt6VrQ'
group by
1
) on Day = Date
and block_timestamp = max
where
address = '3rBnnH9TTgd3xwu48rnzGsaQkSr1hR64nY71DrDt6VrQ' group by 1
ORDER BY day DESC
Run a query to Download Data