0xHaM-dTotal Chain
    Updated 2025-01-22
    -- forked from Over Time Final @ https://flipsidecrypto.xyz/studio/queries/ac8036d9-8444-4bdb-ac71-39a34bec2f50

    WITH priceTb as (
    select
    hour,
    PRICE
    from crosschain.price.ez_prices_hourly
    where symbol ='ETH'
    and BLOCKCHAIN = 'ethereum'
    -- and date_trunc(day,hour)::date >= '2024-12-01'
    and TOKEN_ADDRESS is NULL
    )
    , swapTB as (
    SELECT
    BLOCK_TIMESTAMP,
    TX_HASH,
    FROM_ADDRESS,
    TX_FEE_PRECISE as FEE_PRECISE,
    TX_FEE_PRECISE*PRICE as FEE_USD,
    FROM ink.core.fact_transactions
    JOIN priceTb on trunc(BLOCK_TIMESTAMP, 'hour') = hour
    )
    SELECT
    trunc(BLOCK_TIMESTAMP, 'd') as date,
    count(DISTINCT TX_HASH) as n_txs,
    count(DISTINCT FROM_ADDRESS) as n_users,
    sum(FEE_PRECISE) as TX_FEE_ETH,
    sum(FEE_USD) as TX_FEE_USD,
    TX_FEE_USD/n_txs as fee_usd_per_tx,
    sum(n_txs) over (order by date) as total_n_txs,
    sum(TX_FEE_ETH) over (order by date) as total_TX_FEE_ETH,
    sum(TX_FEE_USD) over (order by date) as total_TX_FEE_USD
    FROM swapTB
    GROUP by 1
    ORDER by 1 DESC
    --*/
    QueryRunArchived: QueryRun has been archived