Query editor
WITHprices AS (SELECT minute,contract_address,priceFROM prices.usdWHERE minute > now() - interval '7 days'), pair_volume AS (selectCASEWHEN (t.token_a_address)::VARCHAR > (t.token_b_address::VARCHAR)THEN (t.token_a_address::VARCHAR) || '-' || (t.token_b_address::VARCHAR)ELSE (t.token_b_address::VARCHAR) || '-' || (t.token_a_address::VARCHAR)END AS pair, (CASE WHEN token_a_address = a.contract_address THEN token_a_amount * a.price -- Use token A when there's USD price for itELSE token_b_amount * b.price -- Else use token bEND) AS usd_volumeFROM dex.trades tLEFT JOIN prices a ON date_trunc('minute', block_time) = a.minute AND token_a_address = a.contract_addressLEFT JOIN prices b ON date_trunc('minute', block_time) = b.minute AND token_b_address = b.contract_addressWHEREblock_time > (now() - interval '7 days')), total_volume AS (SELECTSUM(usd_volume) AS usd_volumeFROM pair_volume)SELECTROW_NUMBER() OVER (ORDER BY SUM(pv.usd_volume) DESC NULLS LAST) AS rank, pv.pair, SUM(pv.usd_volume) AS usd_volume, (SUM(pv.usd_volume) / tv.usd_volume)*100 AS percent_of_volume, (SUM((SUM(pv.usd_volume) / tv.usd_volume)*100) OVER (ORDER BY SUM(pv.usd_volume) DESC)) AS cumulative_volume_percent, PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY pv.usd_volume) as p25_trade_size, PERCENTILE_CONT(0.50) WITHIN GROUP (ORDER BY pv.usd_volume) as median_trade_size, PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY pv.usd_volume) as p75_trade_size, PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY pv.usd_volume) as p95_trade_sizeFROM pair_volume pv, total_volume tvGROUP BY pv.pair, tv.usd_volumeORDER BY 3 DESC NULLS LAST;