Query editor
-- Parameters are text, not date: the Dune execution API rejects the 'date'-- parameter type. Cast in SQL, matching canonical query 6964551.WITH params AS (SELECTCAST('{{start_date}}' AS DATE) AS start_date,CAST('{{end_date}}' AS DATE) AS end_date),tokens AS (SELECT DISTINCT symbolFROM dune.xstocks.result_latest_x_stocks_assetsWHERE solana_address IS NOT NULL),prints AS (SELECTt.block_date AS day,t.block_time,t.tx_index,CASE WHEN t.token_bought_symbol IN (SELECT symbol FROM tokens) THEN t.token_bought_symbolELSE t.token_sold_symbol END AS symbol,CASE WHEN t.token_bought_symbol IN (SELECT symbol FROM tokens)THEN t.amount_usd / NULLIF(t.token_bought_amount, 0)ELSE t.amount_usd / NULLIF(t.token_sold_amount, 0) END AS px,t.amount_usdFROM dex_solana.trades tCROSS JOIN params pWHERE t.block_date BETWEEN p.start_date AND p.end_dateAND (t.token_bought_symbol IN (SELECT symbol FROM tokens)OR t.token_sold_symbol IN (SELECT symbol FROM tokens))AND t.amount_usd BETWEEN 250 AND 5000000),med AS (SELECT day, symbol, APPROX_PERCENTILE(px, 0.5) AS px_medFROM printsWHERE px BETWEEN 0.01 AND 100000GROUP BY day, symbol),clean AS (SELECT p.*FROM prints pJOIN med m ON m.day = p.day AND m.symbol = p.symbolWHERE p.px BETWEEN m.px_med * 0.8 AND m.px_med * 1.2)SELECTday,symbol,MIN_BY(px, (block_time, tx_index)) AS open,MAX(px) AS high,MIN(px) AS low,MAX_BY(px, (block_time, tx_index)) AS close,SUM(amount_usd) AS volume_usd,COUNT(*) AS tradesFROM cleanGROUP BY day, symbolORDER BY symbol, day