Abbas_ra21Top Borrowers 2.5
    Updated 2024-02-25
    -- forked from Top Borrowers 2 @ https://flipsidecrypto.xyz/edit/queries/bb6d56c3-55ba-451e-940d-e2200f7bfe83

    with tb AS (select
    BORROWER AS "Borrower Address",
    sum(
    case
    when Amount_USD is not null then Amount_USD
    else Amount * Price
    end
    ) AS Volume,
    Count(*) AS "Borrow Count"
    from
    base.defi.ez_lending_borrows A
    inner join base.price.ez_hourly_token_prices B on B.TOKEN_ADDRESS = A.TOKEN_ADDRESS
    and HOUR = date_trunc('hour', BLOCK_TIMESTAMP)
    where
    PLATFORM = 'Seamless'
    group by
    1 order by 2 desc limit 200)
    select
    date_trunc('Day', BLOCK_TIMESTAMP) AS Day,
    PROJECT_NAME AS Project,
    count(*) AS TX,
    rank() over (partition by Day order by TX desc) AS Rank
    from base.core.fact_transactions
    inner join base.core.dim_labels on to_address=Address
    where FROM_ADDRESS in (select "Borrower Address" from tb)
    and label_type not in ('operator','token','chadmin')
    group by 1,2 qualify (rank <= 10)





    QueryRunArchived: QueryRun has been archived