WITH APT_price AS (

SELECT

hour,

MEDIAN(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 t1.EVENT_DATA:input_amount / 1e8

ELSE t1.EVENT_DATA:quote_volume / 1e8

END AS volume_apt,

volume_apt * 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,

t2.event_data:instantaneous_stats:market_cap / 1e8 AS market_cap_apt,

market_cap_apt * hp.price AS market_cap_usd,

t2.event_data:instantaneous_stats:total_value_locked / 1e8 AS tvl_apt,

tvl_apt * hp.price AS tvl_usd,

t2.event_data:last_swap:avg_execution_price_q64::FLOAT / POWER(2, 64) AS swap_price,

t2.event_data:cumulative_stats:n_swaps AS total_swaps,

t2.event_data:cumulative_stats:integrator_fees / 1e8 AS integrator_fees_apt,

integrator_fees_apt * hp.price AS integrator_fees,

t2.event_data:cumulative_stats:pool_fees_quote / 1e8 AS pool_fees_apt,

pool_fees_apt * hp.price AS pool_fees,

ROW_NUMBER() OVER (PARTITION BY emoji, market ORDER BY block_timestamp DESC) AS row_num