Query editor
-- Phantom 底层交易数据提取-- 最终输出:按天 × 数据源的聚合统计报表WITH swaps_30 AS (SELECT*FROMdune.okxweb3wallet.result_jupiter_swap_dataWHEREblock_time > (now() + INTERVAL '8' HOUR) - INTERVAL '30' day),-- Step 1: 获取 log_index = 1 的 input_symbol 和相关信息initial_input AS (SELECTtx_id,input_symbol AS initial_input_symbol,input_mint AS initial_input_mint,input_decimals AS initial_input_decimals,input_usd AS initial_input_usdFROMswaps_30WHERElog_index = 1),-- Step 2: 获取 log_index 最大的 output_symbol 和相关信息initial_output AS (SELECTtx_id,output_symbol AS final_output_symbol,output_mint AS final_output_mint,output_decimals AS final_output_decimals,output_usd AS final_output_usdFROMswaps_30WHERE(tx_id, log_index) IN (SELECTtx_id,MAX(log_index)FROMswaps_30GROUP BYtx_id)),-- Step 3: 汇总 input_symbol 的 input_amountsummed_input AS (SELECTe.tx_id,SUM(e.input_amount) AS total_input_amount,SUM(e.input_usd) AS total_input_usdFROMswaps_30 AS eJOIN initial_input AS i ON e.tx_id = i.tx_idWHEREe.input_mint = i.initial_input_mintGROUP BY