MasiWeekly Loans
Updated 2024-10-07
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- 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