WITH 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

),

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 FROM pool_addresses)

-- AND block_timestamp > '2022-01-01'

GROUP BY 1,2

),

last_block_per_pool_daily AS (

SELECT date_trunc('day',block_timestamp) date, user_address, max(block_number) last_block

FROM ethereum.core.ez_balance_deltas

WHERE user_address IN (SELECT pool_address FROM pool_addresses)

-- AND block_timestamp > '2022-01-01'

GROUP BY date, user_address

),

pool_tvls_wHoles AS (

SELECT D.*, A.platform, A.pool_name

FROM (

SELECT block_timestamp::date date, B.user_address, sum(current_bal_usd) pool_tvl_usd

FROM ethereum.core.ez_balance_deltas D RIGHT JOIN last_block_per_pool_daily B ON (D.user_address = B.user_address AND D.block_number = B.last_block)

GROUP BY 1,2

HAVING pool_tvl_usd < 1e10

) D LEFT JOIN pool_addresses A ON D.user_address = A.pool_address