Query editor
-- Market Capitalization for PURPLE Token (Counter Display)-- PURPLE Token: 0xD642B49d10cc6e1BC1c6945725667c35e0875f22WITH recent_prices AS (-- Get most recent price from each DEX with volume in last 24hSELECTdt.project as dex_name,dt.token_bought_address,dt.token_sold_address,dt.amount_usd /CASEWHEN dt.token_bought_address = 0xD642B49d10cc6e1BC1c6945725667c35e0875f22THEN dt.token_bought_amount_raw / POW(10, COALESCE(tb.decimals, 18))ELSE dt.token_sold_amount_raw / POW(10, COALESCE(ts.decimals, 18))END as price_usd,dt.block_time,ROW_NUMBER() OVER (PARTITION BY dt.project ORDER BY dt.block_time DESC) as rnFROM dex.trades dtLEFT JOIN tokens.erc20 tb ON tb.contract_address = dt.token_bought_address AND tb.blockchain = 'ink'LEFT JOIN tokens.erc20 ts ON ts.contract_address = dt.token_sold_address AND ts.blockchain = 'ink'WHEREdt.block_time >= NOW() - interval '24' hourAND (dt.token_bought_address = 0xD642B49d10cc6e1BC1c6945725667c35e0875f22OR dt.token_sold_address = 0xD642B49d10cc6e1BC1c6945725667c35e0875f22)AND dt.project IN ('velodrome', 'inkyswap', 'uniswap')AND dt.amount_usd > 0),dex_volumes AS (-- Get 24h volume by DEX for weightingSELECTproject as dex_name,SUM(amount_usd) as volume_24hFROM dex.tradesWHEREblock_time >= NOW() - interval '24' hourAND (token_bought_address = 0xD642B49d10cc6e1BC1c6945725667c35e0875f22OR token_sold_address = 0xD642B49d10cc6e1BC1c6945725667c35e0875f22)AND project IN ('velodrome', 'inkyswap', 'uniswap')GROUP BY 1),price_comparison AS (-- Compare prices and determine which to useSELECTrp.dex_name,rp.price_usd,dv.volume_24h,rp.block_time,RANK() OVER (ORDER BY dv.volume_24h DESC) as volume_rankFROM recent_prices rpJOIN dex_volumes dv ON rp.dex_name = dv.dex_nameWHERE rn = 1),final_price AS (-- Select price based on volume or discrepancy rulesSELECTCASEWHEN COUNT(*) = 1 THEN MAX(price_usd)