Query editor
WITH agg_trade AS (SELECT *FROM dex."trades"WHERE category = 'Aggregator'AND block_time < NOW() - INTERVAL '{{Number_Of_Days}} days'AND block_time > NOW() - INTERVAL '{{Number_Of_Days}} days' - INTERVAL '{{Interval}} days'ORDER BY block_time DESC),arb_list AS (SELECT tx_fromFROM(SELECT COUNT(tx_hash) AS tx,tx_fromFROM dex."trades"WHERE project = 'Uniswap'AND version = '3'AND block_time < NOW() - INTERVAL '{{Number_Of_Days}} days'AND block_time > NOW() - INTERVAL '{{Number_Of_Days}} days' - INTERVAL '{{Interval}} days'GROUP BY 2) AS tWHERE tx > 10 --- addresses that with less than 20 tx over the last 24 hours),uni_trade AS(SELECT *FROM dex."trades"WHERE project = 'Uniswap'AND version = '3'AND block_time < NOW() - INTERVAL '{{Number_Of_Days}} days'AND block_time > NOW() - INTERVAL '{{Number_Of_Days}} days' - INTERVAL '{{Interval}} days'ORDER BY block_time DESC),b_volume AS (SELECT SUM(u.usd_amount) AS b_amount, --- Aggregator trading volume over last 24 hoursdate_trunc('hour',u."block_time") AS hourFROM uni_trade uINNER JOIN agg_trade aON a.tx_hash = u.tx_hashWHERE a.tx_from NOT IN (SELECT tx_fromFROM arb_list)GROUP BY 2ORDER BY 2),total_volume AS(SELECT SUM(usd_amount) AS total_amount,date_trunc('hour',"block_time") AS hourFROM uni_trade uGROUP BY 2ORDER BY 2),a_volume AS(SELECT SUM(usd_amount) AS a_amount, --- arbitrager trading volume over last 24 hoursdate_trunc('hour',"block_time") AS hourFROM dex."trades"WHERE tx_from IN (SELECT tx_fromFROM arb_list)AND project = 'Uniswap'AND version = '3'AND block_time < NOW() - INTERVAL '{{Number_Of_Days}} days'AND block_time > NOW() - INTERVAL '{{Number_Of_Days}} days' - INTERVAL '{{Interval}} days'GROUP BY 2ORDER BY 2),b_count AS (SELECT COUNT(u.tx_hash) AS b_tx_count, --- Aggregator tx count over last 24 hours