CarlOwOsvolume by hour
Updated 2022-05-27
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
›
⌄
with sell as (select block_timestamp
, to_numeric(replace(attribute_value, 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4',''))*pow(10,-6) as amount
from osmosis.core.fact_msg_attributes
where msg_type = 'token_swapped'
and date(block_timestamp) >= '2022-03-20'
and attribute_key='tokens_in'
and attribute_value like '%ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4'
),
sell_by_hour as (select date_trunc('HOUR', to_time(block_timestamp)) as time
, sum(amount) as h_s_amount
from sell
where dayofweek(block_timestamp) = 5 --WEEKS START ON MONDAY
group by time
),
buy as (select block_timestamp
, to_numeric(replace(attribute_value, 'ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4',''))*pow(10,-6) as amount
from osmosis.core.fact_msg_attributes
where msg_type = 'token_swapped'
and date(block_timestamp) >= '2022-03-20'
and attribute_key='tokens_out'
and attribute_value like '%ibc/987C17B11ABC2B20019178ACE62929FE9840202CE79498E29FE8E5CB02B7C0A4'
),
buy_by_hour as (select date_trunc('HOUR', to_time(block_timestamp)) as time
, sum(amount) as h_b_amount
from buy
where dayofweek(block_timestamp) = 5
group by time
)
select s.time, h_s_amount, h_b_amount
from sell_by_hour s
join buy_by_hour b on s.time=b.time
order by time
Run a query to Download Data