flipside

    distribution of tx
    Specterdistribution of tx
    Updated 2024-11-20

    WITH ETHprice AS (
    SELECT
    TRUNC(hour, 'day') AS day,
    AVG(price) AS price
    FROM ethereum.price.ez_prices_hourly
    WHERE token_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
    GROUP BY day
    ),

    -- deposit
    deposit AS (
    SELECT
    tx_id,
    tx_to AS user,
    amount / POW(10, decimal) AS amounts,
    amounts * ep.price AS amount_usd
    FROM eclipse.core.fact_transfers ft
    JOIN ETHprice ep ON TRUNC(ft.block_timestamp, 'day') = ep.day
    WHERE tx_from = 'F7p3dFrjRTbtRp8FRF6qHLomXbKRBzpvBLjtQcfcgmNe'
    AND succeeded = 'TRUE'
    ),

    user_tx_count AS (
    SELECT
    user,
    COUNT(DISTINCT tx_id) AS tx_count,
    SUM(amount_usd) AS total_amount_usd
    FROM deposit
    GROUP BY user
    )

    SELECT
    COUNT(DISTINCT user) AS Users,
    CASE
    WHEN tx_count = 1 THEN '1'
    QueryRunArchived: QueryRun has been archived