pool_addresses AS (

SeLeCt

contract_address as pool_address,

any_value(pool_name) pool_name,

any_value(platform) platform

FROM

ethereum.core.ez_dex_swaps

WHERE

platform IN ('uniswap-v2', 'uniswap-v3', 'sushiswap')

AND amount_in_usd is not NULL

AND amount_out_usd is not NULL

AND block_timestamp > '2022-01-01'

GROUP BY

pool_address

HAVING

count(distinct sender) > 20

),

-- Next calculate daily and weekly tx volume by pool, filter pools with low weekly tx-counts

pool_stats_daily AS (

SELECT

block_timestamp::date as date,

contract_address as pool_address,

any_value(pool_name) pool_name,

any_value(platform) platform,

count(distinct tx_hash) as tx_count,

sum(amount_in_usd + amount_out_usd) / 2 as usd_volume,

count(distinct sender) as unique_users

FROM

ethereum.core.ez_dex_swaps

WHERE

contract_address IN (

SELECT

pool_address