cptjackAnchor Borrow Rate - Daily copy
999
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
›
⌄
-- forked from jp12 / Anchor Borrow Rate - Daily @ https://flipsidecrypto.xyz/jp12/q/oDzKXmfMOO1M/anchor-borrow-rate-daily
WITH deposited as (
SELECT DATE(block_timestamp) as date,
SUM(msg_value:coins[0]:amount / POW(10, 6)) as deposited
FROM terra.msgs
WHERE msg_value:contract::string = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s'
AND msg_value:execute_msg:deposit_stable IS NOT NULL and tx_status = 'SUCCEEDED'
GROUP BY 1
ORDER BY 1
)
, redeemed as (
SELECT DATE(block_timestamp) as date,
SUM(msg_value:execute_msg:send:amount / POW(10, 6)) as redeemed
FROM terra.msgs
WHERE msg_value:contract::string = 'terra1hzh9vpxhsk8253se0vv5jj6etdvxu3nv8z07zu'
AND msg_value:execute_msg:send:msg:redeem_stable IS NOT NULL
AND tx_status = 'SUCCEEDED'
GROUP BY 1
ORDER BY 1
)
, net_deposit as (
SELECT d.date,
deposited - redeemed as net_deposited
FROM deposited d INNER JOIN redeemed r ON d.date = r.date
)
, borrows AS (
SELECT
TRUNC(block_timestamp,'day') as date,
SUM(msg_value:execute_msg:borrow_stable:borrow_amount) / 1e6 AS total_borrowed
FROM terra.msgs
WHERE
msg_value:contract = 'terra1sepfj7s0aeg5967uxnfk4thzlerrsktkpelm5s' AND
Run a query to Download Data