picasogeneral state
    Updated 2025-05-15
    WITH base_data AS (
    SELECT
    DATE_TRUNC('day', ft.BLOCK_TIMESTAMP) AS tx_day,
    ft.TX_HASH,
    ft.from_address,
    ft.TX_FEE,
    ph.PRICE AS token_price_usd
    FROM
    avalanche.core.fact_transactions ft
    LEFT JOIN avalanche.price.ez_prices_hourly ph
    ON DATE_TRUNC('hour', ft.BLOCK_TIMESTAMP) = ph.HOUR
    AND ph.SYMBOL = 'AVAX'
    WHERE
    ft.BLOCK_TIMESTAMP >= '2025-01-01 00:00:00.000'
    ),
    daily_stats AS (
    SELECT
    tx_day,
    COUNT(DISTINCT TX_HASH) AS tx_count,
    COUNT(DISTINCT FROM_ADDRESS) AS address_count,
    SUM(COALESCE(TX_FEE * token_price_usd, 0)) AS total_fee_usd
    FROM base_data
    GROUP BY tx_day
    ),
    contract_deploys AS (
    SELECT
    COUNT(DISTINCT TX_HASH) AS total_contracts_deployed
    FROM
    avalanche.core.fact_traces
    WHERE
    TYPE ILIKE 'create%'
    AND TO_ADDRESS IS NOT NULL
    AND INPUT IS NOT NULL
    AND INPUT != '0x'
    AND TX_SUCCEEDED = TRUE
    AND TRACE_SUCCEEDED = TRUE
    Last run: 2 months ago
    TOTAL_TRANSACTIONS
    TOTAL_ADDRESSES
    TOTAL_FEE_USD
    AVG_FEE_PER_DAY
    AVG_FEE_PER_TX
    AVG_TX_PER_ADDRESS
    AVG_FEE_PER_ADDRESS
    AVG_ADDRESSES_PER_DAY
    AVG_TX_PER_DAY
    TOTAL_CONTRACTS_DEPLOYED
    1
    4774640579323411893583.5117043414026.540.03976.020.238758758.08353677.07519127
    1
    91B
    98s