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