headitmanagerSolend Demographics
    Updated 2022-07-31
    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