lanomars 999 - 8 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 jackguy / mars 999 - 8 @ https://flipsidecrypto.xyz/jackguy/q/GO5NmJPtHNGx/mars-999-8
with tab1 as (
SELECT
tx_id,
ATTRIBUTE_VALUE,
block_timestamp
FROM osmosis.core.fact_msg_attributes
WHERE tx_id in (
SELECT DISTINCT tx_id
FROM osmosis.core.fact_msg_attributes
where attribute_value = 'osmo1c3ljch9dfw5kf52nfwpxd2zmj2ese7agnx0p9tenkrryasrle5sqf3ftpg'
AND ATTRIBUTE_KEY LIKE '_contract_address'
) AND attribute_value IN ('borrow', 'deposit', 'repay', 'withdraw')
), tab2 as (
SELECT
tx_id,
CASE when attribute_value LIKE 'uosmo' THEN 'OSMO'
WHEN attribute_value LIKE 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858' then 'axlUSDC'
WHEN attribute_value LIKE 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2' then 'ATOM' END as token
FROM osmosis.core.fact_msg_attributes
WHERE tx_id in (SELECT tx_id from tab1)
AND attribute_key LIKE 'denom'
), tab3 as (
SELECT
tx_id,
replace(replace(REPLACE(attribute_value, 'uosmo', ''), 'ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858', ''), 'ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2', '') / power(10, 6) as amt
FROM osmosis.core.fact_msg_attributes
WHERE tx_id in (SELECT tx_id from tab1)
AND attribute_key LIKE 'amount'
AND (
attribute_value LIKE '%uosmo%'
OR attribute_value LIKE '%ibc/D189335C6E4A68B513C10AB227BF1C1D38C746766278BA3EEB4FB14124F1D858%'
OR attribute_value LIKE '%ibc/27394FB092D2ECCD56123C74F36E4C1F926001CEADA9CA97EA622B25F41E5EB2%'
)
)
Run a query to Download Data