with token_info as ( select * from ( VALUES ('ARB','0xb50721bcf8d664c30412cfbc6cf7a15145234ad1'), ('axlARB' , '0xb50721bcf8d664c30412cfbc6cf7a15145234ad1'), ('APE' , '0x4d224452801aced8b2f0aebe155379bb5d594381' ), ('ARX' , '0x7d8daff6d70cead12c6f077048552cf89130a2b1'), ('AXL' , '0x467719ad09025fcc6cf6f8311755809d45a5e5f3'), ('BAL' , '0xba100000625a3754423978a60c9317c58a424e3d'), ('axlBAL' , '0xba100000625a3754423978a60c9317c58a424e3d'), -- ----------------------------------------------------------------- ('BUSD' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('axlBUSD' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('AvalancheUSDC' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('PolygonUSDC' , '0xdac17f958d2ee523a2206206994597c13d831ec7' ), ('DAI' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('USDC' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('USDT' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('axlUSDC' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('axlUSDT' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('FRAX' , '0xdac17f958d2ee523a2206206994597c13d831ec7' ), ('sFRAX' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('axlFRAX' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('axlDAI' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), ('cUSD' , '0xdac17f958d2ee523a2206206994597c13d831ec7'), -- ----------------------------------------------------------------- ('wstETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('sfrxETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('rETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('frxETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('YieldETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('axlYieldETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('axl-wstETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('WETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), ('axlETH' , '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'), -- ------------------------------------------------------------------ ('DEUS' , '0xde5ed76e7c05ec5e4572cfc88d1acea165109e44'), ('axlDEUS' , '0xde5ed76e7c05ec5e4572cfc88d1acea165109e44'), ('DOT' , '0x7083609fce4d1d8dc0c979aab8c869ea2c873402'), ('axlDOT' , '0x7083609fce4d1d8dc0c979aab8c869ea2c873402' ), ('ERN' , '0xc5b001dc33727f8f26880b184090d3e252470d45'), ('stERN' , '0xc5b001dc33727f8f26880b184090d3e252470d45'), ('EUROC' , '0x1abaea1f7c830bd89acc67ec4af516284b1bc33c'), ('axlEUROC' , '0x1abaea1f7c830bd89acc67ec4af516284b1bc33c'), ('FXS' , '0x3432b6a60d23ca0dfca7761b7ab56459d9c964d0'), ('GDX' , '0x2f27118e3d2332afb7d165140cf1bb127ea6975d'), ('HUAHUA' , 'ibc/B9E0A1A524E98BB407D3CED8720EFEFD186002F90C1B1B7964811DD0CCC12228'), ('HZN' , '0xc0eff7749b125444953ef89682201fb8c6a917cd'), ('KNC' , '0xdefa4e8a7bcba345f687a2f1456f5edd9ce97202'), ('axlKNC' , '0xdefa4e8a7bcba345f687a2f1456f5edd9ce97202'), ('KTC' , '0x545356d4d69d8cd1213ee7e339867574738751ca'), ('KUJI' , '0x96543ef8d2c75c26387c1a319ae69c0bee6f3fe7'), ('LINK' , '0x514910771af9ca656af840dff83e8264ecf986ca'), ('axlLINK' , '0x514910771af9ca656af840dff83e8264ecf986ca'), ('Lqdr' , '0x816e21c33fa5f8440ebcdf6e01d39314541bea72'), ('LUNA' , 'ibc/785AFEC6B3741100D15E7AF01374E3C4C36F24888E96479B1C33F5C71F364EF9'), ('axlLqdr' , '0x816e21c33fa5f8440ebcdf6e01d39314541bea72'), ('MKR' , '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2'), ('axlMKR' , '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2'), ('MPX' , '0x66eed5ff1701e6ed8470dc391f05e27b1d0657eb'), ('OATH' , '0x21ada0d2ac28c3a5fa3cd2ee30882da8812279b6'), ('OP' , '0x4200000000000000000000000000000000000042'), ('axlOP' , '0x4200000000000000000000000000000000000042'), ('ORBS' , '0xff56cc6b1e6ded347aa0b7676c85ab0b3d08b0fa'), ('OVN' , '0x3b08fcd15280e7b5a6e404c4abb87f7c774d1b2e'), ('OX' , '0x78a0a62fba6fb21a83fe8a3433d44c73a4017a6f'), ('PEPE' , '0x6982508145454ce325ddbe47a25d4ec3d2311933'), ('axlPEPE' , '0x6982508145454ce325ddbe47a25d4ec3d2311933'), ('POLS' , '0x83e6f1e41cdd28eaceb20cb649155049fac3d5aa'), ('RING' , '0x9469d013805bffb7d3debe5e7839237e535ec483'), ('RAI' , '0x03ab458634910aad20ef5f1c8ee96f1d6ac54919'), ('SHIB' , '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce'), ('axlSHIB' , '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce'), ('SONNE' , '0x1db2466d9f5e10d7090e7152b68d62703a2245f0'), ('TORI' , '0xc71e20e54adfc415f79bf0a8f11122917920050e'), ('UNI' , '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984'), ('axlUNI' , '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984'), ('axlTIA' , 'ibc/D79E7D83AB399BFFF93433E54FAA480C191248FC556924A2A8351AE2638B3877'), ('TIA' , 'ibc/D79E7D83AB399BFFF93433E54FAA480C191248FC556924A2A8351AE2638B3877'), ('milkTIA' , 'ibc/D79E7D83AB399BFFF93433E54FAA480C191248FC556924A2A8351AE2638B3877'), ('UST' , '0xa693b19d2931d498c5b318df961919bb4aee87a5'), ('VELA' , '0x088cd8f5ef3652623c22d48b1605dcfe860cd704'), ('WAVAX' , '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'), ('axlAVAX' , '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7'), ('WBNB' , '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c'), ('axlBNB' , '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c'), ('WBTC' , '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599'), ('axlWBTC' , '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599'), ('axlFIL' , '0x0d8ce2a99bb6e3b7db580ed848240e4a0f9ae153'), ('WFIL' , '0x0d8ce2a99bb6e3b7db580ed848240e4a0f9ae153'), ('WFTM' , '0x4e15361fd6b4bb609fa63c81a2be19d873717870'), ('axlFTM' , '0x4e15361fd6b4bb609fa63c81a2be19d873717870'), ('WMATIC' , '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270'), ('axlMATIC' , '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270'), ('axlSOMM' , 'ibc/9BBA9A1C257E971E38C1422780CE6F0B0686F0A3085E2D61118D904BFE0F5F5E'), ('axlATOM' , 'uatom'), ('WGLMR' , '0xacc15dc74880c9944775448304b263d191c6077f'), ('axlGLMR' , '0xacc15dc74880c9944775448304b263d191c6077f'), ('WMAI' , '0xa3fa99a148fa48d14ed51d610c367c61876997f1'), ('axlWMAI' , '0xa3fa99a148fa48d14ed51d610c367c61876997f1') ) as a (symbol,token_address) ) , base as ( select id, created_at, amount, INITCAP(call : chain) as src_chain, INITCAP(call : returnValues : destinationChain) as dest_Chain, call : returnValues : symbol as symbol, b.token_address, lower(coalesce (call : transaction : to , call : returnValues : destinationContractAddress)) as contract, call : transaction : "from" as sender, SIMPLIFIED_STATUS, status from axelar.axelscan.fact_gmp a left join token_info b ON a.call : returnValues : symbol = b.symbol ) , prices as ( select hour, token_address, symbol, price from crosschain.price.ez_prices_hourly ), final as ( select b.* , amount * p.price as amount_usd from base b left join prices p ON b.token_address = p.token_address and date_trunc('hour', created_at) = p.hour ) select CASE WHEN amount_usd < 10 THEN 'less than 10$' WHEN amount_usd < 100 THEN '10$ < volume < 100$' WHEN amount_usd < 1000 THEN '100$ < volume < 1k$' WHEN amount_usd < 10000 THEN '1k$ < volume < 10k$' WHEN amount_usd < 100000 THEN '10k$ < volume < 100k$' WHEN amount_usd < 1000000 THEN '100k$ < volume < 1m$' WhEN amount_usd > 1000000 Then 'more than 1m$' else 'Other' END AS type, sum(amount_usd) as volume, count(DISTINCT sender) as n_users, count(DISTINCT id) as n_txns from final where contract = '0xce16f69375520ab01377ce7b88f5ba8c48f8d666' group by 1

Squid Router