SELECT

swapper,

COUNT(DISTINCT tx_hash) AS total_transactions,

SUM(amount_in_usd) AS total_swap_volume_usd

FROM

aptos.defi.ez_dex_swaps

WHERE

block_timestamp >= '2024-01-01'

AND block_timestamp <= '2024-12-31'

GROUP BY

swapper

HAVING

total_swap_volume_usd >= 5000

AND total_transactions > 50

),

user_fees AS (

SELECT

swaps.swapper,

SUM((t.gas_used * t.gas_unit_price) / 1e8) AS total_fees_in_apt,

AVG((t.gas_used * t.gas_unit_price) / 1e8) AS avg_fee_per_transaction_in_apt

FROM

aptos.defi.ez_dex_swaps swaps

JOIN

aptos.core.fact_transactions t

ON swaps.tx_hash = t.tx_hash

WHERE

swaps.block_timestamp >= '2024-01-01'

AND swaps.block_timestamp <= '2024-12-31'

AND t.success = TRUE

GROUP BY

swaps.swapper

u.swapper,

u.total_transactions,