freemartianPools - User Activities
Updated 2024-01-04
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 tbl AS (
SELECT *
FROM osmosis.core.fact_msg_attributes
WHERE msg_type IN ('withdraw_position','create_position')
AND attribute_key = 'pool_id'
AND attribute_value IN ('1325','1337')
AND block_timestamp::date >= '2023-01-10'
ORDER BY tx_id ASC, attribute_index ASC
),
tbl2 AS(
SELECT
block_timestamp, tx_id, msg_type,attribute_index, attribute_key, attribute_value
FROM osmosis.core.fact_msg_attributes
WHERE tx_id IN (SELECT tx_id FROM tbl)
AND msg_type IN ('withdraw_position','create_position','add_to_position')
ORDER BY tx_id ASC, attribute_index ASC
),
datas AS(
SELECT
block_timestamp,
tx_id,
msg_type,
object_agg(CAST(ATTRIBUTE_KEY AS VARCHAR), CAST(ATTRIBUTE_VALUE AS VARIANT)) AS obj
FROM tbl2
WHERE tx_id IN (SELECT tx_id FROM tbl)
GROUP BY 1,2,3;
),
ranked AS (
SELECT
block_timestamp,
tx_id,
msg_type,
obj,
QueryRunArchived: QueryRun has been archived