SELECT tx_hash

FROM ethereum.core.ez_token_transfers

WHERE from_address = LOWER('{{account}}')

), -- Get all hashes of TXs executed from your address

base_details AS (

SELECT DISTINCT

s.tx_hash,

s.symbol_in,

s.symbol_out

FROM base_tx b

JOIN ethereum.defi.ez_dex_swaps s ON b.tx_hash = s.tx_hash

), -- Pass all your TX hashes thru the dex_swaps table and find every match (i.e. find all your dex swaps)

random_txs AS (

SELECT

bd.symbol_in,

bd.symbol_out,

s.tx_hash

FROM base_details bd

JOIN ethereum.defi.ez_dex_swaps s ON bd.symbol_in = s.symbol_in AND bd.symbol_out = s.symbol_out

WHERE s.tx_hash NOT IN (SELECT tx_hash FROM base_details)

ORDER BY RANDOM()

LIMIT 25 -- Limit to 25 random transactions. Adjust as needed.

), -- Find the TX hash of 25 random transactions that swap the same assets on the same platform

avg_gas_random_txs AS (

SELECT

rt.symbol_in,

rt.symbol_out,

AVG(t.gas_used) AS avg_gas_used_random

FROM random_txs rt

JOIN ethereum.core.fact_transactions t ON rt.tx_hash = t.tx_hash

GROUP BY rt.symbol_in, rt.symbol_out

) -- Calc the gas for the random transactions using the TX hashes from above just as you did for your TXs at the top.