Query editor
WITH-- Define important addressesdex_addresses AS (SELECT address, 'DEX' as entity_type, protocol_nameFROM (VALUES(0x1b02dA8Cb0d097eB8D57A175b88c7D8b47997506, 'SushiSwap'),(0xa5E0829CaCEd8fFDD4De3c43696c57F7D7A678ff, 'QuickSwap'),(0x68b3465833fb72A70ecDF485E0e4C7bD8665Fc45, 'Uniswap V3'),(0xE592427A0AEce92De3Edee1F18E0157C05861564, 'Uniswap V3 Router')) AS t(address, protocol_name)),cex_addresses AS (SELECT address, 'CEX' as entity_type, cex_name as protocol_nameFROM (VALUES(0x0639556F03714A74a5fEEaF5736a4A64fF70D206, 'Binance'),(0xf89d7b9c864f589bbF53a82105107622B35EaA40, 'Bybit'),(0x5bdf85216ec1e38d6458c870992a69e38e03f7ef, 'Crypto.com'),(0x46340b20830761efd32832A74d7169B29FEB9758, 'OKX')) AS t(address, cex_name)),-- Main transfers dataall_transfers AS (SELECTDATE_TRUNC('day', evt_block_time) as date,evt_block_time,"from" as sender,"to" as receiver,contract_address,CAST(value AS DOUBLE) / POWER(10, 6) as amount_usd,-- Classify transfer typeCASEWHEN dex1.address IS NOT NULL OR dex2.address IS NOT NULL THEN 'DEX'WHEN cex1.address IS NOT NULL OR cex2.address IS NOT NULL THEN 'CEX'ELSE 'P2P'END as transfer_type,-- Get CEX name if applicableCOALESCE(cex1.protocol_name, cex2.protocol_name) as cex_nameFROM erc20_polygon.evt_Transfer tLEFT JOIN dex_addresses dex1 ON t."to" = dex1.addressLEFT JOIN dex_addresses dex2 ON t."from" = dex2.addressLEFT JOIN cex_addresses cex1 ON t."to" = cex1.addressLEFT JOIN cex_addresses cex2 ON t."from" = cex2.addressWHEREt.contract_address IN (0x2791Bca1f2de4661ED88A30C99A7a9449Aa84174, -- USDC.e0xc2132D05D31c914a87C6611C10748AEb04B58e8F, -- USDT0x8f3Cf7ad23Cd3CaDbD9735AFf958023239c6A063, -- DAI0x3c499c542cEF5E3811e1192ce70d8cC03d5c3359 -- USDC)AND evt_block_time >= CURRENT_DATE - INTERVAL '90' dayAND value > 0AND "from" != 0x0000000000000000000000000000000000000000AND "to" != 0x0000000000000000000000000000000000000000),-- METRIC 1: Overall Daily Metrics