bobby_danieltotal 🌐 stats copy
    Updated 2025-03-07
    -- forked from mr_d / total 🌐 stats @ https://flipsidecrypto.xyz/mr_d/q/FOJINf2r4Ilu/total-stats

    WITH APT_price AS (
    SELECT
    hour,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price) AS price
    FROM aptos.price.ez_prices_hourly
    WHERE symbol = 'APT'
    GROUP BY hour
    ),

    transactions_data AS (
    SELECT
    t1.block_timestamp,
    t1.TX_HASH,
    t1.EVENT_DATA:swapper AS trader,
    CASE
    WHEN t1.EVENT_DATA:is_sell = FALSE
    THEN CAST(t1.EVENT_DATA:input_amount AS FLOAT) / 1e8
    ELSE CAST(t1.EVENT_DATA:quote_volume AS FLOAT) / 1e8
    END AS volume_apt,
    (CAST(t1.EVENT_DATA:input_amount AS FLOAT) / 1e8) * hp.price AS volume_usd,
    TRY_HEX_DECODE_STRING(SUBSTRING(t2.EVENT_DATA:market_metadata:emoji_bytes, 3)) AS emoji,
    t2.EVENT_DATA:market_metadata:market_address AS market,
    CAST(t2.event_data:instantaneous_stats:market_cap AS FLOAT) / 1e8 AS market_cap_apt,
    (CAST(t2.event_data:instantaneous_stats:market_cap AS FLOAT) / 1e8) * hp.price AS market_cap_usd,
    CAST(t2.event_data:instantaneous_stats:total_value_locked AS FLOAT) / 1e8 AS tvl_apt,
    (CAST(t2.event_data:instantaneous_stats:total_value_locked AS FLOAT) / 1e8) * hp.price AS tvl_usd,
    CAST(t2.event_data:last_swap:avg_execution_price_q64 AS FLOAT) / POWER(2, 64) AS swap_price,
    CAST(t2.event_data:cumulative_stats:n_swaps AS INT) AS total_swaps,
    CAST(t2.event_data:cumulative_stats:integrator_fees AS FLOAT) / 1e8 AS integrator_fees_apt,
    (CAST(t2.event_data:cumulative_stats:integrator_fees AS FLOAT) / 1e8) * hp.price AS integrator_fees,
    CAST(t2.event_data:cumulative_stats:pool_fees_quote AS FLOAT) / 1e8 AS pool_fees_apt,
    (CAST(t2.event_data:cumulative_stats:pool_fees_quote AS FLOAT) / 1e8) * hp.price AS pool_fees,
    ROW_NUMBER() OVER (PARTITION BY emoji, market ORDER BY block_timestamp DESC) AS row_num
    FROM aptos.core.fact_events t1
    QueryRunArchived: QueryRun has been archived