Query editor
WITH daily AS (SELECTDATE_TRUNC('day', block_time) AS day,SUM(bytearray_to_uint256(bytearray_substring(data, 161, 32)) / 1e30) AS volume_usd,0 AS fees_usd,0 AS swap_volume_usdFROM base.logsWHERE contract_address = 0xbc2DC9feB0a8202eE4e3B908f55549684bf1C4e8AND (topic0 = 0x2fe68525253654c21998f35787a8d0f361905ef647c854092430ab65f2f15022OR topic0 = 0x93d75d64d1f84fc6f430a64fc578bdd4c1e090e90ea2d51773e626d19de56d30)GROUP BY 1UNION ALLSELECTDATE_TRUNC('day', block_time) AS day,0 AS volume_usd,SUM(bytearray_to_uint256(bytearray_substring(data, 33, 32)) / 1e30) AS fees_usd,0 AS swap_volume_usdFROM base.logsWHERE contract_address = 0xbc2DC9feB0a8202eE4e3B908f55549684bf1C4e8AND (topic0 = 0x47cd9dda0e50ce30bcaaacd0488452b596221c07ac402a581cfae4d3933cac2bOR topic0 = 0x5d0c0019d3d45fadeb74eff9d2c9924d146d000ac6bcf3c28bf0ac3c9baa011a)GROUP BY 1UNION ALLSELECTDATE_TRUNC('day', block_time) AS day,0 AS volume_usd,0 AS fees_usd,SUM(CASEWHEN bytearray_substring(data, 77, 20) = 0x4200000000000000000000000000000000000006THEN bytearray_to_uint256(bytearray_substring(data, 161, 32)) / 1e18 * 2050WHEN bytearray_substring(data, 77, 20) = 0x0555E30da8f98308EdB960aa94C0Db47230d2B9cTHEN bytearray_to_uint256(bytearray_substring(data, 161, 32)) / 1e8 * 85000WHEN bytearray_substring(data, 77, 20) = 0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913THEN bytearray_to_uint256(bytearray_substring(data, 161, 32)) / 1e6ELSE 0END) AS swap_volume_usdFROM base.logsWHERE contract_address = 0xbc2DC9feB0a8202eE4e3B908f55549684bf1C4e8AND topic0 = 0x0874b2d545cb271cdbda4e093020c452328b24af12382ed62c4d00f5c26709dbGROUP BY 1),aggregated AS (SELECTday,SUM(volume_usd) AS daily_position_volume_usd,SUM(swap_volume_usd) AS daily_swap_volume_usd,SUM(volume_usd) + SUM(swap_volume_usd) AS daily_total_volume_usd,SUM(fees_usd) AS daily_fees_usdFROM daily