flipside

    Terra 2 astroport 2
    jackguyTerra 2 astroport 2
    Updated 2023-05-02
    with swaps as (
    SELECT
    block_timestamp,
    tx_id,
    label,
    MESSAGE_VALUE:sender as sender,
    CASE WHEN attributes:wasm:amount_0 is NULL THEN attributes:wasm:offer_amount ELSE attributes:wasm:amount_0 END / power(10,6) as val_1,
    CASE WHEN attributes:wasm:amount_1 is NULL THEN attributes:wasm:return_amount ELSE attributes:wasm:amount_1 END / power(10,6) as val_2,
    attributes:wasm:ask_asset as asset_1,
    attributes:wasm:offer_asset as asset_2
    FROM terra.core.fact_messages
    LEFT outer JOIN terra.core.dim_address_labels
    ON message_value:contract = address
    WHERE label LIKE '%astroport%'
    AND (label LIKE '%axlusdc%' OR label LIKE '%axlusdt%')
    AND label_subtype LIKE 'pool'
    AND MESSAGE_VALUE:msg:swap is not null
    )

    SELECT
    date_trunc('day', block_timestamp) as day,
    label as pool_name,
    sum(CASE WHEN asset_1 LIKE 'CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF' OR asset_1 LIKE 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4' THEN val_1
    WHEN asset_2 LIKE 'CBF67A2BCF6CAE343FDF251E510C8E18C361FC02B23430C121116E0811835DEF' OR asset_2 LIKE 'ibc/B3504E092456BA618CC28AC671A71FB08C6CA0FD0BE7C8A5B5A3E2DD933CC9E4' THEN val_2 END) as swap_volume,
    count(DISTINCT tx_id) as swaps,
    COUNT(DISTINCT sender) as users
    from swaps
    GROUP BY 1,2

    --WHERE tx_id LIKE '78B61DD961E295805BE78FCDBC12D953568B5FB40D91E80A974FD3BE919B09B1'

    --GROUP BY 1
    --ORDER BY 2 DESC
    --LIMIT 20
    Run a query to Download Data