jackguy2023-04-28 02:28 PM
Updated 2023-04-28
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 filtered_transactions AS (
SELECT DISTINCT tx_id AS unique_hash
FROM osmosis.core.fact_msg_attributes
WHERE
ATTRIBUTE_KEY = 'stream_id' AND
ATTRIBUTE_VALUE = '1'
),
subscribed_transactions AS (
SELECT DISTINCT a.tx_id AS sub_hash
FROM osmosis.core.fact_msg_attributes a
INNER JOIN filtered_transactions ft ON a.tx_id = ft.unique_hash
WHERE
a.ATTRIBUTE_KEY = 'action' AND
a.ATTRIBUTE_VALUE = 'subscribe_pending'
),
sender_wallets AS (
SELECT DISTINCT a.ATTRIBUTE_VALUE AS user_wallet, st.sub_hash
FROM osmosis.core.fact_msg_attributes a
INNER JOIN subscribed_transactions st ON a.tx_id = st.sub_hash
WHERE
a.ATTRIBUTE_KEY = 'sender'
),
transaction_data AS (
SELECT DISTINCT sw.user_wallet, sw.sub_hash, a.ATTRIBUTE_VALUE / power(10, 6) AS trans_amount, date_trunc('hour', a.block_timestamp) AS trans_time
FROM osmosis.core.fact_msg_attributes a
INNER JOIN sender_wallets sw ON a.tx_id = sw.sub_hash
WHERE
a.ATTRIBUTE_KEY = 'in_amount'
)
SELECT
-- date_trunc('hour', td.trans_time) as hour,
COUNT(DISTINCT td.user_wallet) AS wallet_count,
Run a query to Download Data