nitsUntitled Query
Updated 2022-05-19
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 kashi_contracts as (SELECT * from ethereum.contracts
where contains(meta:name,'Kashi')),
withdrawals as
(SELECT *, 'out' as direction from ethereum.udm_events
where
-- tx_id ilike '0xbffe832d33d27d9f2bb4e9c2c91aee162d333b4f234795a69a15b0e51d510a33' and
to_address = '0x0000000000000000000000000000000000000000'
and contract_address in (SELECT address from kashi_contracts)),
deposits as
(SELECT *, 'IN' as direction from ethereum.udm_events
where
-- tx_id ilike '0xbffe832d33d27d9f2bb4e9c2c91aee162d333b4f234795a69a15b0e51d510a33' and
from_address = '0x0000000000000000000000000000000000000000'
and contract_address in (SELECT address from kashi_contracts)),
-- where tx_id ilike '0x5f541b83b1546a2205206c2afe5fa2a2e97c87403301bd7a82372698baf4702c'
all_txs as
(SELECT tx_id as txid, amount_usd as amt_usd
from ethereum.udm_events
where tx_id in (SELECT tx_id from withdrawals UNION ALL SELECT tx_id from deposits) and amount_usd is not NULL
) ,
deposits_usd as
(SELECT *, 'd' as action from deposits
inner join all_txs
on tx_id = txid ),
withdrawals_usd as
(SELECT *, 'w' as action from withdrawals
inner join all_txs
on tx_id = txid )
-- SELECT symbol, sum(amt_net) as tvl
-- SELECT date(block_timestamp), count(DISTINCT origin_address) as total_borrowers
SELECT *
from
(SELECT *, case when action = 'd' then amt_usd else amt_usd *(-1) end as amt_net from
(SELECT * from deposits_usd
UNION ALL
Run a query to Download Data