Query editor
WITH agg AS (SELECT block_date as day, tx_hash,MIN(CASE projectWHEN '0x API' THEN '0x API'WHEN 'cow_protocol' THEN 'CoW Protocol'ELSE projectEND) as source,SUM(amount_usd) as agg_volFROM dex_aggregator.tradesWHERE blockchain = 'ink'AND block_date >= DATE(CURRENT_DATE - INTERVAL '7' DAY)AND block_date <= CURRENT_DATEAND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '7' DAY)GROUP BY 1, 2),fills AS (SELECT tx_hash, SUM(amount_usd) as fill_volFROM dex.tradesWHERE blockchain = 'ink'AND block_date >= DATE(CURRENT_DATE - INTERVAL '7' DAY)AND block_date <= CURRENT_DATEAND block_month >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '7' DAY)AND tx_hash IN (SELECT tx_hash FROM agg)GROUP BY 1)SELECT a.day, a.source,count(CASE WHEN f.tx_hash IS NULL OR f.fill_vol < a.agg_vol * 0.95 THEN 1 END) as n_txs,ROUND(SUM(CASEWHEN f.tx_hash IS NULL THEN a.agg_volWHEN f.fill_vol < a.agg_vol * 0.95 THEN a.agg_vol - f.fill_volELSE 0END)) as volume_usdFROM agg aLEFT JOIN fills f ON f.tx_hash = a.tx_hashGROUP BY 1, 2HAVING SUM(CASEWHEN f.tx_hash IS NULL THEN a.agg_volWHEN f.fill_vol < a.agg_vol * 0.95 THEN a.agg_vol - f.fill_volELSE 0END) > 0ORDER BY day, volume_usd DESC