dan-smithAssets Deposited and Borrowed copy
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 txs as (
select
distinct a.tx_id,
action
from (
select
tx_id,
attribute_value as action
from osmosis.core.fact_msg_attributes
where attribute_key = 'action' and
(attribute_value = 'borrow' or attribute_value = 'deposit' or attribute_value = 'withdraw' or attribute_value = 'repay'
)) a
left join osmosis.core.fact_msg_attributes b
on a.tx_id = b.tx_id
where b.attribute_key = '_contract_address' and b.attribute_value = 'osmo1c3ljch9dfw5kf52nfwpxd2zmj2ese7agnx0p9tenkrryasrle5sqf3ftpg'
),
capital_flows as (
select
date_trunc('hour',a.block_timestamp) as dt,
a.tx_id,
b.action,
d.token as asset,
a.amount*e.price/pow(10,d.decimal) as amount_usd
from (
select
block_timestamp,
tx_id,
attribute_value as amount
from osmosis.core.fact_msg_attributes
where msg_type = 'wasm' and attribute_key = 'amount'
) a
join txs b
on a.tx_id = b.tx_id
left join (
Run a query to Download Data