Query editor
WITH RawVolume AS (-- 聚合所有 OrderFilled 事件,计算原始的“双重计算”交易量 (Volume_A)SELECTdate_trunc('day', evt_block_time) AS tx_day,evt_tx_hash, -- 用于 JOIN,确保同一交易内的事件被正确分组SUM(CASE-- 原始逻辑:只要 USDC (Asset ID = 0) 侧有成交量就计入WHEN TRY_CAST(makerAssetId AS VARCHAR) = '0' THEN TRY_CAST(makerAmountFilled AS DOUBLE)WHEN TRY_CAST(takerAssetId AS VARCHAR) = '0' THEN TRY_CAST(takerAmountFilled AS DOUBLE)END) AS raw_volume_usdc_sum -- 未除以 1e6,方便后续计算FROM (SELECT * FROM polymarket_polygon.CTFExchange_evt_OrderFilledUNION ALLSELECT * FROM polymarket_polygon.NegRiskCtfExchange_evt_OrderFilled) AS all_fillsWHERE evt_block_time >= (current_date - interval '7' day)GROUP BY 1, 2),CorrectedVolume AS (-- 聚合只保留 Taker 是交易所合约的事件,计算修正后的“单边”交易量 (Volume_B)SELECTdate_trunc('day', evt_block_time) AS tx_day,evt_tx_hash,SUM(CASE-- 逻辑不变:计算 USDC 侧成交量WHEN TRY_CAST(makerAssetId AS VARCHAR) = '0' THEN TRY_CAST(makerAmountFilled AS DOUBLE)WHEN TRY_CAST(takerAssetId AS VARCHAR) = '0' THEN TRY_CAST(takerAmountFilled AS DOUBLE)END) AS corrected_volume_usdc_sum -- 未除以 1e6,方便后续计算FROM (-- CTFExchangeSELECT * FROM polymarket_polygon.CTFExchange_evt_OrderFilledWHERE taker = 0x4bfb41d5b3570defd03c39a9a4d8de6bd8b8982eUNION ALL-- NegRiskCtfExchangeSELECT * FROM polymarket_polygon.NegRiskCtfExchange_evt_OrderFilledWHERE taker = 0xc5d563a36ae78145c45a50134d48a1215220f80a) AS corrected_fillsWHERE evt_block_time >= (current_date - interval '7' day)GROUP BY 1, 2)SELECTT1.tx_day,-- 原始(双重计算)交易量 (Volume_A)SUM(T1.raw_volume_usdc_sum) / 1e6 AS Volume_A_Raw_Double_Counted,-- 修正(单边计算)交易量 (Volume_B)SUM(T2.corrected_volume_usdc_sum) / 1e6 AS Volume_B_Corrected_Single_Side,