germanUsers borrowed from Solend (SOL)
Updated 2022-08-03
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
›
⌄
⌄
/* What is the user distribution by size of account? (How many have between $1-1,000, 1,000 - 100k, etc).
Is the protocol largely influenced by whales, or by average Solana users?*/
with users_sol_borrow as (
SELECT distinct signers[0] as users--inner_instructions[1]:instructions[1]:parsed:info:destination as sol_user
FROM solana.core.fact_transactions
WHERE instructions[0]:programId = 'So1endDq2YkqhipRh3WViPa8hdiSpxWy6z3Z6tMCpAo'
AND inner_instructions[1]:instructions[1]:parsed:info:authority = 'DdZR6zRFiUt4S5mg7AV1uKB2z1f1WzcNYCaTEEWPAuby'
--and PRE_TOKEN_BALANCES[0]:mint = 'So11111111111111111111111111111111111111112'
and PRE_TOKEN_BALANCES[0]:owner = 'DdZR6zRFiUt4S5mg7AV1uKB2z1f1WzcNYCaTEEWPAuby'
and block_timestamp >= '2022-02-01'
and succeeded = 'TRUE'),
balances as (
SELECT
distinct ( post_token_balances[0]:owner) as address,
max(block_timestamp) as last_date,
case when post_token_balances[0]:mint = 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v' then 'USD'
else 'USD' END AS token,
sum(post_token_balances[0]:uiTokenAmount:amount / 1e9) as amount
FROM solana.core.fact_transactions t
JOIN users_sol_borrow u ON t.post_token_balances[0]:owner = u.users
WHERE block_timestamp::date >= '2022-06-03'
AND address = signers[0]
AND post_token_balances[0]:mint in ( 'EPjFWdd5AufqSSqeM2qN1xzybapC8G4wEGGkZwyTDt1v', 'Es9vMFrzaCERmJfrF4H2FYD4KCoNkY11McCe8BenwNYB')
GROUP BY 1,post_token_balances HAVING amount > 0),
categories as (
SELECT address, amount,
case when amount <10000 then 'small_user'
when amount >=10000 and amount <= 1000000 then 'avg_user'
when amount >1000000 then 'whale_user' end as category
FROM balances )
Run a query to Download Data