DAY | OPEN_LOAN_COUNT | CUMULATIVE_OPEN_LOAN_COUNT | |
|---|---|---|---|
1 | 2025-04-27 00:00:00.000 | 0 | 3177 |
2 | 2025-04-26 00:00:00.000 | 0 | 3177 |
3 | 2025-04-25 00:00:00.000 | 0 | 3177 |
4 | 2025-04-24 00:00:00.000 | 0 | 3177 |
5 | 2025-04-23 00:00:00.000 | 0 | 3177 |
6 | 2025-04-22 00:00:00.000 | 0 | 3177 |
7 | 2025-04-21 00:00:00.000 | 0 | 3177 |
8 | 2025-04-20 00:00:00.000 | 0 | 3177 |
9 | 2025-04-19 00:00:00.000 | 0 | 3177 |
10 | 2025-04-18 00:00:00.000 | 0 | 3177 |
11 | 2025-04-17 00:00:00.000 | 0 | 3177 |
12 | 2025-04-16 00:00:00.000 | 0 | 3177 |
13 | 2025-04-15 00:00:00.000 | 0 | 3177 |
14 | 2025-04-14 00:00:00.000 | 0 | 3177 |
15 | 2025-04-13 00:00:00.000 | 0 | 3177 |
16 | 2025-04-12 00:00:00.000 | 0 | 3177 |
17 | 2025-04-11 00:00:00.000 | 0 | 3177 |
18 | 2025-04-10 00:00:00.000 | 0 | 3177 |
19 | 2025-04-09 00:00:00.000 | 0 | 3177 |
20 | 2025-04-08 00:00:00.000 | 0 | 3177 |
pietrektOpen Loan Count
Updated 2025-04-27
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
›
⌄
with open_loan AS
(select to_date(block_timestamp) as day, count(*) as open_count
from thorchain.defi.fact_loan_open_events
group by day),
close_loan AS (select tx_id, block_timestamp,
owner, collateral_asset, collateral_withdrawn,
(SELECT COUNT(*) FROM thorchain.defi.fact_loan_open_events as a
where a.collateral_asset = b.collateral_asset and
a.block_timestamp < b.block_timestamp AND
UPPER(a.owner) = UPPER(b.owner)) AS close_loan_weight
from thorchain.defi.fact_loan_repayment_events as b
where collateral_withdrawn > 0),
close_loan_corrected AS (
select to_date(block_timestamp) as day,
COALESCE(close_loan_weight - LAG(close_loan_weight) OVER(PARTITION BY owner, collateral_asset ORDER BY block_timestamp), close_loan_weight) AS close_loan_weight
from close_loan
),
close_loan_daily AS (
select day, sum(close_loan_weight) as close_loan_weight from close_loan_corrected group by day),
joined AS (select DISTINCT(a.block_date) as day, COALESCE(open_count, 0) as open_count, COALESCE(close_loan_weight, 0) as close_count
from thorchain.core.dim_block as a
left join open_loan as b on a.block_date = b.day left join close_loan_daily as c on a.block_date = c.day)
select day, open_count - close_count as open_loan_count,
SUM(open_loan_count) OVER(ORDER BY day) as cumulative_open_loan_count
from joined
Last run: about 1 month ago
...
1456
48KB
3s