sinahosseinzadehUntitled Query
Updated 2022-08-02
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
›
⌄
with labels as (
select *
from solana.core.dim_labels
where label = 'solend'
),
solend_transactions as (
select INNER_INSTRUCTION:instructions[0]:parsed:info:authority as account,* from
solana.core.fact_events
where block_timestamp:: date > '2022-07-01'
and program_id in (select address from labels)
and succeeded = TRUE
),
balances as (
select block_timestamp, signers[0] as wallet, tx_id, post_balances[0] / pow(10,9) as balance
from solana.core.fact_transactions
--LEFT JOIN table(flatten(input => pre_balances)) pre
-- LEFT JOIN table(flatten(input => post_balances)) post
where block_timestamp:: date > '2022-07-01'
and tx_id in (select tx_id from solend_transactions)
),
wallets as (
select wallet, avg(balance) as avg_balance, count(distinct(tx_id)) as tx
from balances
group by 1
)
select
CASE
when avg_balance > 100000 then '>100000'
when avg_balance > 10000 then '>10000'
when avg_balance > 10000 then '>10000'
when avg_balance > 1000 then '>1000'
when avg_balance > 100 then '>100'
when avg_balance > 10 then '>10'
when avg_balance > 1 then '>1'
else '<=1'
END as balances,
Run a query to Download Data