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/92BE0717F4678905E53F4E45B2DED18BC0CB97BF1F8B6A25AFEDF3D5A879B4D5','uosmo')

    ),
    deposits as (
    SELECT
    trunc(block_timestamp,'day') as date,
    sum(amount) as daily_volume,
    sum(daily_volume) over (order by date) as cum_volume
    from rami where actions ='deposit'
    group by 1
    order by 1 asc
    Run a query to Download Data