WITH arbitrum_data AS (

SELECT

block_timestamp,

is_taker,

digest,

trader,

subaccount,

amount_usd,

fee_amount

FROM

arbitrum.vertex.ez_perp_trades

WHERE

block_timestamp >= TIMESTAMP '2024-07-01'

AND trader != '0x0000000000000000000000000000000000000000'

AND subaccount != '0x0000000000000000000000000000000000000000000000000000000000000001'

COUNT(DISTINCT trader) AS unique_traders,

COUNT(DISTINCT digest) AS total_orders,

SUM(CASE WHEN is_taker = FALSE THEN amount_usd ELSE 0 END) AS total_volume,

SUM(fee_amount) AS total_fees,

--- Metrics for the last 24 hours

COUNT(DISTINCT CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 HOURS' THEN digest END) AS perp_trades_24hrs,

SUM(CASE WHEN block_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 HOURS' AND is_taker = FALSE THEN amount_usd ELSE 0 END) AS volume_24hrs

FROM arbitrum_data;