MasiWeekly Loans
    Updated 2024-10-07
    -- price
    with tb0 as (select trunc(hour,'day') as day,
    case when symbol = 'WETH' then 'ETH' else 'BTC' end as token,
    avg(price) as avg_price
    from ethereum.price.ez_prices_hourly
    where symbol in ('WETH','WBTC')
    group by 1,2
    )
    ,
    tb0_0 as ( select trunc(hour,'day') as daily,
    symbol as token,
    avg(price) as "Rune Price"
    from thorchain.price.ez_prices_hourly
    where hour::date >= '2023-08-21'
    group by 1,2)
    ,
    -- Collateral
    tb1 as ( select trunc(block_timestamp,'day') as day,
    owner,
    FACT_LOAN_OPEN_EVENTS_ID,
    case when COLLATERAL_ASSET = 'BTC.BTC' then 'BTC' else 'ETH' end as collateral_token,
    case when COLLATERAL_ASSET = 'BTC.BTC' then COLLATERAL_DEPOSITED/pow(10,8) else COLLATERAL_DEPOSITED/pow(10,8) end as collateral_amount,
    DEBT_ISSUED/pow(10,8) as debt_amount,
    TARGET_ASSET
    from thorchain.defi.fact_loan_open_events)
    ,
    tb1_1 as ( select day,
    collateral_token,
    sum(collateral_amount) as coll_amount,
    sum(debt_amount) as dept_volume,
    count(DISTINCT FACT_LOAN_OPEN_EVENTS_ID) as Loans_open
    from tb1
    group by 1,2
    )
    ,
    -- Repayment