MLDZMNebr2
    Updated 2023-05-25
    -- forked from br1 @ https://flipsidecrypto.xyz/edit/queries/37dd9902-0fb3-44e0-adb9-0bbb26fa66e7

    with t1 as ( select
    hour::date as day,
    avg(price) as avg_price
    from ethereum.core.fact_hourly_token_prices
    where symbol = 'WETH'
    group by 1)

    select
    s.block_timestamp::date as date,
    count(distinct ORIGIN_FROM_ADDRESS) as borrowers,
    count(distinct a.tx_hash) as no_borrow,
    sum(tx_fee) as paid_fee,
    sum(tx_fee*avg_price) as paid_fee_usd,
    sum(ETH_VALUE*avg_price) as volume_usd,
    sum(paid_fee_usd) over (order by date) as total_fee_usd,
    sum(ethereum.public.udf_hex_to_int(substring(INPUT_DATA, 188, 15))::float/1e18) as volume_borrow,
    sum((ethereum.public.udf_hex_to_int(substring(INPUT_DATA, 188, 15))::float/1e18)*avg_price) as volume_borrow_usd,
    avg((ethereum.public.udf_hex_to_int(substring(INPUT_DATA, 188, 15))::float/1e18)*avg_price) as avg_borrow_usd,
    sum(volume_usd) over (order by date) as total_volume_usd
    from ethereum.core.fact_event_logs s
    left join ethereum.core.fact_transactions a on s.tx_hash=a.tx_hash
    left join t1 on s.block_timestamp::date=t1.day
    where s.CONTRACT_ADDRESS=lower('0xfb3330531E3f98671296f905cd82CC407d90CE97')
    and s.ORIGIN_FUNCTION_SIGNATURE='0xfbf37739'
    group by 1
    order by 1


    Run a query to Download Data