flipside

    precise-pink
    Kruys-Collinsprecise-pink
    Updated 2025-03-17
    WITH
    -- Define the token contract address as a variable
    token_params AS (
    SELECT lower('0xb2F85b7AB3c2b6f62DF06dE6aE7D09c010a5096E') AS token_address
    ),

    -- Get all relevant transfer activity
    transfers AS (
    SELECT
    BLOCK_TIMESTAMP,
    FROM_ADDRESS,
    TO_ADDRESS,
    AMOUNT,
    AMOUNT_USD,
    TX_HASH
    FROM avalanche.core.ez_token_transfers
    WHERE CONTRACT_ADDRESS = (SELECT token_address FROM token_params)
    ),

    -- Get liquidity pools that contain our token
    liquidity_pools AS (
    SELECT
    POOL_ADDRESS,
    PLATFORM
    FROM avalanche.defi.dim_dex_liquidity_pools
    WHERE
    TOKENS:token0::STRING = LOWER((SELECT token_address FROM token_params))
    OR TOKENS:token1::STRING = LOWER((SELECT token_address FROM token_params))
    ),

    -- Calculate metrics per address per month
    monthly_address_metrics AS (
    SELECT
    address,
    time_period,
    SUM(total_volume_out) AS total_sent_volume,
    Last run: about 1 month ago
    CURRENT_PERIOD
    PREVIOUS_PERIOD
    FROM_SEGMENT
    TO_SEGMENT
    TRANSITION_COUNT
    TRANSITION_PERCENTAGE
    1
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Active TraderActive Trader950
    2
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Active TraderCasual Trader527.78
    3
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Active TraderOther316.67
    4
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Active TraderLiquidity Provider15.56
    5
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Casual TraderTransient1540.54
    6
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Casual TraderCasual Trader1437.84
    7
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Casual TraderLiquidity Provider718.92
    8
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Casual TraderOther12.7
    9
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Liquidity ProviderLiquidity Provider9087.38
    10
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Liquidity ProviderTransient54.85
    11
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Liquidity ProviderCasual Trader43.88
    12
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000Liquidity ProviderOther43.88
    13
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000OtherOther642.86
    14
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000OtherTransient428.57
    15
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000OtherCasual Trader214.29
    16
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000OtherLiquidity Provider214.29
    17
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000TransientTransient1466.67
    18
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000TransientOther419.05
    19
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000TransientCasual Trader29.52
    20
    		2025-03-01 00:00:00.0002025-02-01 00:00:00.000TransientLiquidity Provider14.76
    67
    6KB
    3s