Query editor
with base_table as (-- Ethereumselect'ethereum' as chain, from_hex(substr(cast(data as varchar) , 1, 10)) as method_id, from_hex(substr(cast(data as varchar) , 24+11, 40)) as address, varbinary_to_uint256(from_hex(substr(cast(data as varchar) , 24+11+40, 64))) / 1e6 as usdc_transferred, from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))) as message, substr(from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))),1,5) as prefix, block_timefrom ethereum.transactionswhere from_hex(substr(cast(data as varchar) , 1, 10)) = 0xa9059cbband substr(from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))),1,5) = 'cpis_'and to = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48and block_time >= cast('2024-01-01 00:00' as timestamp)union all-- Polygonselect'polygon' as chain, from_hex(substr(cast(data as varchar) , 1, 10)) as method_id, from_hex(substr(cast(data as varchar) , 24+11, 40)) as address, varbinary_to_uint256(from_hex(substr(cast(data as varchar) , 24+11+40, 64))) / 1e6 as usdc_transferred, from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))) as message, substr(from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))),1,5) as prefix, block_timefrom polygon.transactionswhere from_hex(substr(cast(data as varchar) , 1, 10)) = 0xa9059cbband substr(from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))),1,5) = 'cpis_'and to = 0x3c499c542cef5e3811e1192ce70d8cc03d5c3359and block_time >= cast('2024-01-01 00:00' as timestamp)union all-- Baseselect'base' as chain, from_hex(substr(cast(data as varchar) , 1, 10)) as method_id, from_hex(substr(cast(data as varchar) , 24+11, 40)) as address, varbinary_to_uint256(from_hex(substr(cast(data as varchar) , 24+11+40, 64))) / 1e6 as usdc_transferred, from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))) as message, substr(from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))),1,5) as prefix, block_timefrom base.transactionswhere from_hex(substr(cast(data as varchar) , 1, 10)) = 0xa9059cbband substr(from_utf8(from_hex(substr(cast(data as varchar) , 24+11+40+64, 64))),1,5) = 'cpis_'and to = 0x833589fCD6eDb6E08f4c7C32D4f71b54bdA02913and block_time >= cast('2024-01-01 00:00' as timestamp)), vol_over_time as (selectchain, date_trunc('month', block_time) as evt_month, sum(usdc_transferred) as total_value_usd, count(*) as tx_count, avg(usdc_transferred) as avg_tx_size_usdfrom base_table