pool_address as pair,

token0,

token1,

pool_name

FROM ethereum.dex_liquidity_pools

WHERE

platform = 'uniswap-v2'

AND ((token0 = lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') AND token1 = lower('0xdac17f958d2ee523a2206206994597c13d831ec7')) --WETH-USDT

OR (token0 = lower('0x6b175474e89094c44da98b954eedeac495271d0f') AND token1 = lower('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48')) --DAI-USDC

OR (token0 = lower('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND token1 = lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')) --USDC-WETH

OR (token0 = lower('0x2260fac5e5542a773aa44fbcfedf7c193bc2c599') AND token1 = lower('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')) --WBTC-WETH

OR (token0 = lower('0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND token1 = lower('0xdac17f958d2ee523a2206206994597c13d831ec7'))) --USDC-USDT

),

liq as (

date_trunc('day',balance_date) as day,

a.pool_name,

ROUND(SUM(amount_usd),2) as liq

FROM ethereum.erc20_balances e

INNER JOIN a on a.pair = e.user_address

WHERE balance_date >= getdate() - interval '15 days'

GROUP BY 1,2

ORDER BY 1

),

swaps as (

SELECT

date_trunc('day',block_timestamp) as day,

a.pool_name,

ROUND(SUM(amount_usd),2) as trade

FROM ethereum.dex_swaps d

INNER JOIN a on a.pair = d.pool_address

WHERE block_timestamp >= getdate() - interval '15 days'

GROUP BY 1,2

ORDER BY 1)