MLDZMNnew_users
    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
    date_trunc('day', first_transaction_stamp) as date,
    'Lender' as user_type,
    count (distinct sender) as new_users
    from (
    select
    lender as sender,
    min(block_timestamp) as first_transaction_stamp
    from decoded_date
    group by 1
    )
    where date>='2023-01-01'
    group by 1,2

    union all

    select
    date_trunc('day', first_transaction_stamp) as date,
    'Borrower' as user_type,
    count (distinct sender) as new_users
    from (
    select
    borrower as sender,
    Last run: about 1 year ago
    DATE
    USER_TYPE
    NEW_USERS
    1
    2023-07-21 00:00:00.000Lender13
    2
    2023-09-25 00:00:00.000Lender9
    3
    2023-05-02 00:00:00.000Lender114
    4
    2024-01-10 00:00:00.000Lender3
    5
    2023-12-21 00:00:00.000Lender5
    6
    2023-12-26 00:00:00.000Lender6
    7
    2023-09-30 00:00:00.000Lender2
    8
    2024-01-19 00:00:00.000Lender3
    9
    2023-09-30 00:00:00.000Borrower7
    10
    2024-01-10 00:00:00.000Borrower74
    11
    2023-05-02 00:00:00.000Borrower167
    12
    2024-01-19 00:00:00.000Borrower27
    13
    2023-09-25 00:00:00.000Borrower9
    14
    2023-07-21 00:00:00.000Borrower20
    15
    2023-12-26 00:00:00.000Borrower19
    16
    2023-12-21 00:00:00.000Borrower30
    17
    2024-01-07 00:00:00.000Lender2
    18
    2023-07-22 00:00:00.000Lender20
    19
    2023-07-01 00:00:00.000Lender26
    20
    2023-06-25 00:00:00.000Lender26
    ...
    537
    21KB
    11s