SELECT DISTINCT

block_timestamp,

tx_hash,

contract_address,

symbol,

trader,

amount_usd,

fee_amount,

'perp' AS label

FROM arbitrum.vertex.ez_perp_trades

UNION ALL

SELECT DISTINCT

block_timestamp,

tx_hash,

contract_address,

symbol,

trader,

amount_usd,

fee_amount,

'spot' AS label

FROM arbitrum.vertex.ez_spot_trades

SELECT date_trunc('day', block_timestamp) AS day,

COUNT(DISTINCT CASE WHEN label = 'spot' THEN trader END) AS distinct_spot_traders,

COUNT(DISTINCT CASE WHEN label = 'perp' THEN trader END) AS distinct_perp_traders,

COUNT(DISTINCT trader) AS distinct_total_traders,

COUNT(DISTINCT CASE WHEN label = 'spot' THEN tx_hash END) AS distinct_spot_txns,

COUNT(DISTINCT CASE WHEN label = 'perp' THEN tx_hash END) AS distinct_perp_txns,

COUNT(DISTINCT tx_hash) AS distinct_total_txns,

SUM(CASE WHEN label = 'spot' THEN amount_usd ELSE 0 END) AS total_amount_spot,

SUM(CASE WHEN label = 'perp' THEN amount_usd ELSE 0 END) AS total_amount_perp,

SUM(amount_usd) AS total_amount_both