flipside

    Osmosis: In The Stars v2 lenta
    adriaparcerisasOsmosis: In The Stars v2 lenta
    Updated 2022-05-30
    --Stargaze Zone is an IBC NFT launchpad and marketplace, and new launchpad projects drop on mint every Friday at 4:00 PM EST.
    --Does the volume of swaps involving Stars change around this time?
    --Analyze the volume of swaps involving STARS from March 20th until present.
    --Hint: Use the msg_attributes table and select transactions that have an appropriate msg_type for swaps. The token address for STARS and other tokens can be found by joining to the labels table.
    WITH
    info as (
    select
    tx_id,
    block_timestamp,
    RIGHT(attribute_value, LENGTH(attribute_value) - LENGTH(SPLIT_PART(TRIM(REGEXP_REPLACE(attribute_value, '[^[:digit:]]', ' ')), ' ', 0))) AS currency,
    SPLIT_PART(TRIM(REGEXP_REPLACE(
    attribute_value,
    '[^[:digit:]]',
    ' ')), ' ', 0)/pow(10,6) AS amount
    from osmosis.core.fact_msg_attributes where block_timestamp>='2022-05-20' and msg_type='token_swapped'
    and attribute_key in ('tokens_in','tokens_out')
    )
    SELECT
    trunc(block_timestamp,'day') as date,
    sum(amount) as daily_volume,
    sum(daily_volume) over (order by date) as cum_volume
    from info x
    join osmosis.core.dim_labels y on x.currency=y.address
    where project_name='STARS'
    group by 1
    order by 1 asc
    Run a query to Download Data