headitmanagerSolend Demographics
Updated 2022-07-31
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 csol_tbl as (select date_trunc('day', block_timestamp) as day,instruction:accounts[13] as wallet,
sum(inner_instruction:instructions[1]:parsed:info:amount/1e9) as sol_borrowed
from solana.fact_events
where instruction:programId = 'So1endDq2YkqhipRh3WViPa8hdiSpxWy6z3Z6tMCpAo'
and inner_instruction:instructions[1]:parsed:info:authority = 'DdZR6zRFiUt4S5mg7AV1uKB2z1f1WzcNYCaTEEWPAuby'
and INNER_INSTRUCTION like '%5h6ssFpeDeRbzsEHDbTQNH7nVGgsKrZydxdSTnLm6QdV%'
and succeeded = 'TRUE'
and day >= '2022-07-01'
and instruction:accounts[13] is not null
group by 1 ,2)
,solprice_tbl as (select date_trunc('day', block_timestamp) as day,
avg(swap_to_amount/swap_from_amount) as sol_price
from solana.fact_swaps
where day >= '2022-07-01'
and swap_from_mint = 'So11111111111111111111111111111111111111112'
and swap_to_mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v'
and swap_to_amount > 0
and swap_from_amount > 0
and succeeded = 'TRUE'
group by 1)
,results as (select csol_tbl.day,csol_tbl.wallet,
sol_borrowed * sol_price as sol_borrowed_in_usd
from csol_tbl
join solprice_tbl
on csol_tbl.day = solprice_tbl.day)
select wallet,sum(sol_borrowed_in_usd) as amount, case
when amount>1 and amount <= 1000 then '$1-1,000'
when amount>1000 and amount <= 10000 then '$1,000-10,000'
when amount>10000 and amount <= 100000 then '$10,000-100,000'
when amount>100000 and amount <= 1000000 then '$100,000-1,000,000'
when amount>1000000 then 'above $1,000,000' end as cat , 1
from results
group by wallet
Run a query to Download Data