feyikemiNEAR Txn Fee Trend
    Updated 2024-10-04
    WITH transaction_fees AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    COUNT(DISTINCT tx_hash) AS txn_count,
    SUM(TRANSACTION_FEE / POW(10, 24)) AS total_tx_fee
    FROM near.core.fact_transactions
    WHERE block_timestamp::date >= '2024-01-01'
    GROUP BY date
    ),

    price_data AS (
    SELECT
    DATE_TRUNC('day', hour) AS date,
    AVG(price) AS avg_price
    FROM near.price.ez_prices_hourly
    WHERE symbol = 'LINEAR'
    GROUP BY date
    ),

    daily_fees AS (
    SELECT
    tf.date,
    tf.txn_count,
    (tf.total_tx_fee * pd.avg_price) AS total_fee_usd,
    (tf.total_tx_fee / tf.txn_count) * pd.avg_price AS avg_fee_usd
    FROM transaction_fees tf
    JOIN price_data pd ON tf.date = pd.date
    )

    SELECT
    date,
    txn_count,
    total_fee_usd,
    avg_fee_usd
    FROM daily_fees

    QueryRunArchived: QueryRun has been archived