Query editor
WITH pool_info AS (SELECT pool,fee,fee/1e6 AS pool_fee,token0,token1FROM prjx_hyperevm.uniswapv3factory_evt_poolcreatedORDER BY evt_block_time DESC),dex_trades AS (SELECT *FROM dex.tradesWHERE 1=1AND blockchain = 'hyperevm'AND project = 'prjx'ORDER BY block_time DESC),pool_fees AS (SELECT DATE_TRUNC('day', t.block_time) AS day,p.pool,p.pool_fee,p.token0,p.token1,SUM(COALESCE(t.amount_usd * (p.fee/1e6), 0)) AS total_pool_feesFROM dex_trades tLEFT JOIN pool_info p ON p.pool = t.project_contract_addressGROUP BY 1,2,3,4,5ORDER BY total_pool_fees DESC)SELECT day,t0.symbol || '/' || t1.symbol AS ticker,get_href('https://hyperevmscan.io/address/' || CAST(p.pool AS VARCHAR), t0.symbol || '/' || t1.symbol) AS pool,p.pool AS pool_address,p.pool_fee,p.total_pool_fees,SUM(p.total_pool_fees) OVER (PARTITION BY day) AS daily_cummulative_fee,SUM(p.total_pool_fees) OVER (PARTITION BY day ORDER BY p.total_pool_fees) AS daily_cummulative_fees,(SUM(p.total_pool_fees) OVER (PARTITION BY day ORDER BY p.total_pool_fees)) * 0.86 AS daily_distributed_fees,SUM(p.total_pool_fees) OVER (PARTITION BY p.pool ORDER BY day) AS cummulative_pool_fees,(SUM(p.total_pool_fees) OVER (PARTITION BY p.pool ORDER BY day)) * 0.86 AS total_distributed_pool_fees,SUM(p.total_pool_fees) OVER (ORDER BY day) AS cumulative_fees,(SUM(p.total_pool_fees) OVER (ORDER BY day)) * 0.86 AS cumulative_distributed_feesFROM pool_fees pLEFT JOIN tokens.erc20 t0 on p.token0 = t0.contract_address AND t0.blockchain = 'hyperevm'LEFT JOIN tokens.erc20 t1 on p.token1 = t1.contract_address AND t1.blockchain = 'hyperevm'ORDER BY 1 DESC, p.total_pool_fees DESC