ramishoowUntitled Query
    Updated 2023-02-07
    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