germanCopy of Users borrowed from Solend (SOL) bien hecha
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
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]: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,
sum(post_token_balances[0]:uiTokenAmount:amount / 1e9) as amount,
(SELECT avg(price) as price FROM ethereum.core.fact_hourly_token_prices
WHERE TOKEN_ADDRESS = lower('0xD31a59c85aE9D8edEFeC411D448f90841571b89c')AND HOUR >= '2022-07-20')as price,
(amount * price) as sol_usd
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 = 'So11111111111111111111111111111111111111112'
GROUP BY address HAVING amount > 0),
categories as (
SELECT address, sol_usd,
case when sol_usd <10000 then 'small_user'
when sol_usd >=10000 and sol_usd <= 1000000 then 'avg_user'
when sol_usd >1000000 then 'whale_user' end as category
FROM balances )
Run a query to Download Data