Afonso_Diaz2023-05-11 09:51 PM
Updated 2023-05-11
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 t as (
select
recorded_hour::date as date,
avg(price) as price_usd
from osmosis.core.ez_prices
where symbol = 'ATOM'
group by 1
),
t1 as (
select
tx_id,
a.block_timestamp,
c.attribute_value as user,
d.attribute_value::int/1e6 as amount_atom,
amount_atom * price_usd as amount_usd
from osmosis.core.fact_msg_attributes a
join osmosis.core.fact_msg_attributes b
using(tx_id)
join osmosis.core.fact_msg_attributes c
using(tx_id)
join osmosis.core.fact_msg_attributes d
using(tx_id)
join t on a.block_timestamp::date = date
where a.attribute_key = 'stream_id'
and a.attribute_value = '1'
and b.attribute_key = 'action'
and b.attribute_value = 'subscribe_pending'
and c.attribute_key = 'sender'
and c.attribute_index = '1'
and c.msg_type = 'message'
and d.attribute_key = 'in_amount'
and a.block_timestamp >= '2023-05-04'
)
select
Run a query to Download Data