Specterdistribution of tx
    Updated 2025-04-05
    -- forked from distribution of V @ https://flipsidecrypto.xyz/studio/queries/db031a86-4610-406b-a9ec-9b4023feb160

    WITH EthPrice AS (
    SELECT
    DATE_TRUNC('day', hour) AS date,
    AVG(price) AS price_usd
    FROM ethereum.price.ez_prices_hourly
    WHERE token_address = '0x0a6e7ba5042b38349e437ec6db6214aec7b35676'
    GROUP BY DATE_TRUNC('day', hour)
    ),
    ambient AS (
    SELECT
    Block_timestamp,
    tx_hash,
    origin_from_address AS swapper,
    MAX(CAST(livequery.utils.udf_hex_to_int(data) AS DOUBLE)) / 1e18 AS swell_amount
    FROM swell.core.fact_event_logs
    WHERE contract_address = '0x2826d136f5630ada89c1678b64a61620aab77aea'
    AND origin_to_address IN (
    '0xaaaaaaaa82812f0a1f274016514ba2ca933bf24d',
    '0xc29a46cd4c484bc3cf39409f246f3f27727cd713',
    '0xe5c689d34e03611ac55a603831b00606a8800514'
    )
    AND topic_0 = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef'
    AND Block_timestamp >= '2024-12-24'
    GROUP BY Block_timestamp, tx_hash, origin_from_address
    ),

    UserTxCount AS (
    SELECT
    swapper,
    COUNT(DISTINCT tx_hash) AS total_tx_count
    FROM ambient
    GROUP BY swapper
    )

    Last run: 21 days ago
    TX_BUCKET
    USER_COUNT
    1
    6 - 10 Transactions49
    2
    2 - 5 Transactions332
    3
    More than 10 Transactions43
    4
    1 Transaction1095
    4
    111B
    2s