ramishoowUntitled Query
Updated 2023-02-07
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
rami as (
select
tx_id,
block_timestamp,
case when attribute_value like '%uosmo%' then 'uOSMO'
else 'BNB' end as currency,
case when msg_type ='pool_joined' then 'deposit'
when msg_type ='burn' then 'withdraw' end as actions,
case when currency like '%uOSMO%' then
SPLIT_PART(TRIM(REGEXP_REPLACE(
attribute_value,
'[^[:digit:]]',
' ')), ' ', 0)/pow(10,6)
else SPLIT_PART(TRIM(REGEXP_REPLACE(
attribute_value,
'[^[:digit:]]',
' ')), ' ', 0)/pow(10,18) end AS amount
from osmosis.core.fact_msg_attributes
where
msg_type in ('pool_joined','burn')
and
RIGHT(attribute_value, LENGTH(attribute_value) - LENGTH(SPLIT_PART(TRIM(REGEXP_REPLACE(attribute_value, '[^[:digit:]]', ' ')), ' ', 0))) in ('ibc/F4A070A6D78496D53127EA85C094A9EC87DFC1F36071B8CCDDBD020F933D213D','uosmo')
)
SELECT
trunc(block_timestamp,'day') as date,
currency,
actions,
sum(amount) as daily_volume
from rami where date>='2023-01-01'
group by 1,2,3
Run a query to Download Data