MLDZMNblend7
    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
    block_timestamp::date as date,
    l.LABEL_TYPE as type,
    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,2
    Last run: about 1 year ago
    DATE
    TYPE
    SENDER
    NO_TXN
    1
    2023-01-25 00:00:00.000nft6222584
    2
    2023-01-11 00:00:00.000nft6953247
    3
    2023-08-21 00:00:00.000nft4941251
    4
    2023-10-30 00:00:00.000nft3811130
    5
    2024-01-02 00:00:00.000nft366807
    6
    2023-12-11 00:00:00.000nft5421287
    7
    2023-05-16 00:00:00.000nft5921721
    8
    2023-11-07 00:00:00.000nft6201871
    9
    2023-02-13 00:00:00.000nft6663050
    10
    2023-05-31 00:00:00.000dex180538
    11
    2023-04-15 00:00:00.000defi3850
    12
    2023-07-09 00:00:00.000nft6791912
    13
    2023-02-19 00:00:00.000defi60138
    14
    2023-06-19 00:00:00.000dex99216
    15
    2023-12-02 00:00:00.000dex159481
    16
    2023-07-26 00:00:00.000dex138554
    17
    2023-08-24 00:00:00.000dapp2545
    18
    2023-07-19 00:00:00.000dapp2034
    19
    2023-02-18 00:00:00.000dapp5066
    20
    2023-09-05 00:00:00.000dex97282
    ...
    1556
    63KB
    40s