MLDZMNbrw8
    Updated 2023-03-01
    with tb1 as (select
    BLOCK_TIMESTAMP,
    tx_hash,
    BORROWER_ADDRESS,
    BORROWED_USD
    from ethereum.aave.ez_borrows
    ),

    tb2 as (
    select
    x.BLOCK_TIMESTAMP,
    x.FROM_ADDRESS,
    x.TO_ADDRESS,
    x.tx_hash,
    x.AMOUNT_USD,
    ROW_NUMBER() OVER (partition by FROM_ADDRESS order by x.BLOCK_TIMESTAMP ) as t_n
    from ethereum.core.ez_token_transfers x
    join tb1 y on x.FROM_ADDRESS = y.BORROWER_ADDRESS and x.block_timestamp>y.block_timestamp
    order by 1
    )

    select
    LABEL,
    count(distinct FROM_ADDRESS) as users,
    count(distinct tx_hash) as count_txn,
    sum(AMOUNT_USD) as volume
    -- sum(users) over (partition by LABEL_TYPE order by day) as cum_users,
    -- sum(volume) over (partition by LABEL_TYPE order by date) as cum_txn
    from tb2 q join ethereum.core.dim_labels w on q.to_ADDRESS= w.address
    where LABEL_TYPE not in ('chadmin','operator','token')
    and LABEL not ilike '%aave%'
    and t_n=1
    and BLOCK_TIMESTAMP>=CURRENT_DATE- {{Time_period_days}}
    group by 1 having volume is not null
    order by 4 desc limit 10
    Run a query to Download Data