Query editor
WITHdex_pools as (SELECTproject, version, case when project = 'balancer' and version = '2' then 0xba12222222228d8ba445958a75a0704d566bf2c8 --same vault on all chains, holds tokens for all pools.else project_contract_address end as liquidity_pools, sum(case when block_time > now() - interval '7' day then amount_usd else null end) as seven_day_volume, sum(case when block_time > now() - interval '1' month then amount_usd else null end) as one_month_volume, sum(amount_usd) as total_volumeFROM dex.tradesWHERE blockchain = '{{Chain}}'AND (token_sold_address = {{Token Address}} OR token_bought_address = {{Token Address}})AND block_time >= CAST('{{Start Date}}' AS TIMESTAMP)AND block_time <= CAST('{{End Date}}' AS TIMESTAMP)GROUP BY 1,2,3),balances AS (WITHerc20_in as (SELECT"to" as pool,sum(value) as amount_inFROM erc20_{{Chain}}.evt_Transfer trWHERE tr.contract_address = {{Token Address}}AND "to" IN (SELECT liquidity_pools FROM dex_pools)AND evt_block_time <= CAST('{{End Date}}' AS TIMESTAMP)group by 1),erc20_out as (SELECT"from" as pool,sum(value) as amount_outFROM erc20_{{Chain}}.evt_Transfer trWHERE tr.contract_address = {{Token Address}}AND "from" IN (SELECT liquidity_pools FROM dex_pools)AND evt_block_time <= CAST('{{End Date}}' AS TIMESTAMP)group by 1)SELECTerc20_in.pool, (cast(amount_in as double) - COALESCE(cast(amount_out as double), 0))/pow(10,COALESCE(tk.decimals, 18)) as balanceFROM erc20_inLEFT JOIN erc20_out ON erc20_out.pool = erc20_in.poolLEFT JOIN tokens.erc20 tk ON tk.blockchain = '{{Chain}}' AND tk.contract_address = {{Token Address}}WHERE cast(amount_in as double) - COALESCE(cast(amount_out as double), 0) > 0),pool_addresses AS (SELECTARRAY_AGG(liquidity_pools) as pool_addressesFROMdex_pools),token AS (SELECTsymbol as "symbol",decimals as "decimals"FROM tokens.erc20WHERE blockchain = '{{Chain}}' AND contract_address = {{Token Address}}LIMIT 1