ramishoowUntitled Query
Updated 2023-02-07
9
1
2
3
4
5
6
7
8
9
›
⌄
WITH rami as ( select tx_id, block_timestamp, case when attribute_value like '%uosmo%' then 'uOSMO'
else 'stOSMO' 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,6) 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/D176154B0C63D1F9C6DCFB4F70349EBF2E2B5A87A05902F57A6AE92B863E9AEC','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 order by 1 asc
Run a query to Download Data