MLDZMNblend8
    Updated 2024-01-24
    with decoded_date as (select *,
    concat('0x',substring(data, 155, 40)) AS collection,
    concat('0x',substring(data, 219, 40)) as lender,
    concat('0x',substring(data, 283, 40)) as borrower,
    ethereum.public.udf_hex_to_int(substring(data, 371, 16))::float/1e18 as loan_Amount,
    ethereum.public.udf_hex_to_int(substring(data, 448, 3))::float as rate,
    ethereum.public.udf_hex_to_int(substring(data, 511, 4))::float as tokenID
    from ethereum.core.fact_event_logs
    where contract_address='0x29469395eaf6f95920e59f858042f0e28d98a20b'
    and TOPICS[0] = '0x06a333c2d6fe967ca967f7a35be2eb45e8caeb6cf05e16f55d42b91b5fe31255' --- loantaken
    )


    select
    l.LABEL as project,
    count(distinct t.from_address) as sender,
    count(distinct t.tx_hash) as no_txn
    from ethereum.core.fact_transactions t join ethereum.core.dim_labels l on t.to_address = l.address
    where t.block_timestamp>= '2023-01-01'
    and l.label_subtype != 'token_contract'
    and l.LABEL_TYPE in ('defi','nft','dex','dapp')
    and t.STATUS = 'SUCCESS'
    and t.from_address in (select lender from decoded_date)
    group by 1 having project is not null
    order by 3 desc limit 10
    Last run: about 1 year ago
    PROJECT
    SENDER
    NO_TXN
    1
    blur4088236173
    2
    blur.io2532162514
    3
    uniswap2211117827
    4
    opensea268282648
    5
    looksrare103311015
    6
    uniswap_v34689561
    7
    metamask10028030
    8
    1inch6447807
    9
    x2y210387670
    10
    0x4326131
    10
    219B
    43s