Query editor
WITH trading_data AS (SELECTdate_trunc('day', block_time) as day,CASEWHEN token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol = 'USDC'THEN 'BTC-USDC'WHEN token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol = 'USDC'THEN 'BTC-USDC'WHEN token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol = 'USDT'THEN 'BTC-USDT'WHEN token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol = 'USDT'THEN 'BTC-USDT'END as pair,SUM(amount_usd) as volume_usd,COUNT(*) as trade_countFROM dex.tradesWHERE((token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol IN ('USDC', 'USDT')) OR(token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol IN ('USDC', 'USDT')))AND block_time >= NOW() - INTERVAL '30' dayGROUP BY 1, 2),daily_prices AS (SELECTdate_trunc('day', block_time) as day,CASEWHEN token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol = 'USDC'THEN token_sold_amount / token_bought_amountWHEN token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol = 'USDC'THEN token_bought_amount / token_sold_amountEND as price_usdc,CASEWHEN token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol = 'USDT'THEN token_sold_amount / token_bought_amountWHEN token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol = 'USDT'THEN token_bought_amount / token_sold_amountEND as price_usdt,CASEWHEN (token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol = 'USDC') OR(token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol = 'USDC')THEN 'BTC-USDC'WHEN (token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol = 'USDT') OR(token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol = 'USDT')THEN 'BTC-USDT'END as pairFROM dex.tradesWHERE((token_bought_symbol IN ('BTC', 'WBTC') AND token_sold_symbol IN ('USDC', 'USDT')) OR(token_sold_symbol IN ('BTC', 'WBTC') AND token_bought_symbol IN ('USDC', 'USDT')))AND block_time >= NOW() - INTERVAL '30' dayAND amount_usd > 1000 -- Filter out tiny trades),aggregated_prices AS (SELECTday,pair,CASEWHEN pair = 'BTC-USDC' THEN AVG(price_usdc)