with tab4 as (with tab3 as (with tab1 as (select date_trunc('hour',created_at) as date, id, call:transaction:from as user, call:chain as source_chain, lower(call:returnValues:destinationChain) as destination_chain, amount, call:returnValues:symbol as symbol, case when call:returnValues:symbol='ARB' then '0xb50721bcf8d664c30412cfbc6cf7a15145234ad1' when call:returnValues:symbol='axlARB' then '0xb50721bcf8d664c30412cfbc6cf7a15145234ad1' when call:returnValues:symbol='APE' then '0x4d224452801aced8b2f0aebe155379bb5d594381' -- when call:returnValues:symbol='ARX' then '0x7d8daff6d70cead12c6f077048552cf89130a2b1' when call:returnValues:symbol='AXL' then '0x467719ad09025fcc6cf6f8311755809d45a5e5f3' when call:returnValues:symbol='BAL' then '0xba100000625a3754423978a60c9317c58a424e3d' when call:returnValues:symbol='axlBAL' then '0xba100000625a3754423978a60c9317c58a424e3d' ----------------------------------------------------------------- when call:returnValues:symbol='BUSD' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='axlBUSD' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='AvalancheUSDC' then '0xdac17f958d2ee523a2206206994597c13d831ec7' -- when call:returnValues:symbol='PolygonUSDC' then '0xdac17f958d2ee523a2206206994597c13d831ec7' -- when call:returnValues:symbol='DAI' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='USDC' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='USDT' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='axlUSDC' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='axlUSDT' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='FRAX' then '0xdac17f958d2ee523a2206206994597c13d831ec7' -- when call:returnValues:symbol='sFRAX' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='axlFRAX' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='axlDAI' then '0xdac17f958d2ee523a2206206994597c13d831ec7' when call:returnValues:symbol='cUSD' then '0xdac17f958d2ee523a2206206994597c13d831ec7' ----------------------------------------------------------------- when call:returnValues:symbol='wstETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' when call:returnValues:symbol='sfrxETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' when call:returnValues:symbol='rETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' when call:returnValues:symbol='frxETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' when call:returnValues:symbol='YieldETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' when call:returnValues:symbol='axlYieldETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' -- when call:returnValues:symbol='axl-wstETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' when call:returnValues:symbol='WETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' when call:returnValues:symbol='axlETH' then '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' ------------------------------------------------------------------ when call:returnValues:symbol='DEUS' then '0xde5ed76e7c05ec5e4572cfc88d1acea165109e44' when call:returnValues:symbol='axlDEUS' then '0xde5ed76e7c05ec5e4572cfc88d1acea165109e44' when call:returnValues:symbol='DOT' then '0x7083609fce4d1d8dc0c979aab8c869ea2c873402' when call:returnValues:symbol='axlDOT' then '0x7083609fce4d1d8dc0c979aab8c869ea2c873402' -- when call:returnValues:symbol='ERN' then '0xc5b001dc33727f8f26880b184090d3e252470d45' when call:returnValues:symbol='stERN' then '0xc5b001dc33727f8f26880b184090d3e252470d45' when call:returnValues:symbol='EUROC' then '0x1abaea1f7c830bd89acc67ec4af516284b1bc33c' when call:returnValues:symbol='axlEUROC' then '0x1abaea1f7c830bd89acc67ec4af516284b1bc33c' when call:returnValues:symbol='FXS' then '0x3432b6a60d23ca0dfca7761b7ab56459d9c964d0' when call:returnValues:symbol='GDX' then '0x2f27118e3d2332afb7d165140cf1bb127ea6975d' when call:returnValues:symbol='HUAHUA' then 'ibc/B9E0A1A524E98BB407D3CED8720EFEFD186002F90C1B1B7964811DD0CCC12228' when call:returnValues:symbol='HZN' then '0xc0eff7749b125444953ef89682201fb8c6a917cd' when call:returnValues:symbol='KNC' then '0xdefa4e8a7bcba345f687a2f1456f5edd9ce97202' when call:returnValues:symbol='axlKNC' then '0xdefa4e8a7bcba345f687a2f1456f5edd9ce97202' when call:returnValues:symbol='KTC' then '0x545356d4d69d8cd1213ee7e339867574738751ca' when call:returnValues:symbol='KUJI' then '0x96543ef8d2c75c26387c1a319ae69c0bee6f3fe7' when call:returnValues:symbol='LINK' then '0x514910771af9ca656af840dff83e8264ecf986ca' when call:returnValues:symbol='axlLINK' then '0x514910771af9ca656af840dff83e8264ecf986ca' -- when call:returnValues:symbol='Lqdr' then '0x816e21c33fa5f8440ebcdf6e01d39314541bea72' when call:returnValues:symbol='LUNA' then 'ibc/785AFEC6B3741100D15E7AF01374E3C4C36F24888E96479B1C33F5C71F364EF9' -- when call:returnValues:symbol='axlLqdr' then '0x816e21c33fa5f8440ebcdf6e01d39314541bea72' when call:returnValues:symbol='MKR' then '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2' when call:returnValues:symbol='axlMKR' then '0x9f8f72aa9304c8b593d555f12ef6589cc3a579a2' -- when call:returnValues:symbol='MPX' then '0x66eed5ff1701e6ed8470dc391f05e27b1d0657eb' when call:returnValues:symbol='OATH' then '0x21ada0d2ac28c3a5fa3cd2ee30882da8812279b6' when call:returnValues:symbol='OP' then '0x4200000000000000000000000000000000000042' when call:returnValues:symbol='axlOP' then '0x4200000000000000000000000000000000000042' when call:returnValues:symbol='ORBS' then '0xff56cc6b1e6ded347aa0b7676c85ab0b3d08b0fa' when call:returnValues:symbol='OVN' then '0x3b08fcd15280e7b5a6e404c4abb87f7c774d1b2e' when call:returnValues:symbol='OX' then '0x78a0a62fba6fb21a83fe8a3433d44c73a4017a6f' -- when call:returnValues:symbol='PEPE' then '0x6982508145454ce325ddbe47a25d4ec3d2311933' when call:returnValues:symbol='axlPEPE' then '0x6982508145454ce325ddbe47a25d4ec3d2311933' when call:returnValues:symbol='POLS' then '0x83e6f1e41cdd28eaceb20cb649155049fac3d5aa' when call:returnValues:symbol='RING' then '0x9469d013805bffb7d3debe5e7839237e535ec483' when call:returnValues:symbol='RAI' then '0x03ab458634910aad20ef5f1c8ee96f1d6ac54919' -- when call:returnValues:symbol='SHIB' then '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' when call:returnValues:symbol='axlSHIB' then '0x95ad61b0a150d79219dcf64e1e6cc01f0b64c4ce' -- when call:returnValues:symbol='SONNE' then '0x1db2466d9f5e10d7090e7152b68d62703a2245f0' when call:returnValues:symbol='TORI' then '0xc71e20e54adfc415f79bf0a8f11122917920050e' when call:returnValues:symbol='UNI' then '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' when call:returnValues:symbol='axlUNI' then '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' -- when call:returnValues:symbol='axlTIA' then 'ibc/D79E7D83AB399BFFF93433E54FAA480C191248FC556924A2A8351AE2638B3877' when call:returnValues:symbol='TIA' then 'ibc/D79E7D83AB399BFFF93433E54FAA480C191248FC556924A2A8351AE2638B3877' -- when call:returnValues:symbol='milkTIA' then 'ibc/D79E7D83AB399BFFF93433E54FAA480C191248FC556924A2A8351AE2638B3877' when call:returnValues:symbol='UST' then '0xa693b19d2931d498c5b318df961919bb4aee87a5' when call:returnValues:symbol='VELA' then '0x088cd8f5ef3652623c22d48b1605dcfe860cd704' when call:returnValues:symbol='WAVAX' then '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' when call:returnValues:symbol='axlAVAX' then '0xb31f66aa3c1e785363f0875a1b74e27b85fd66c7' when call:returnValues:symbol='WBNB' then '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c' when call:returnValues:symbol='axlBNB' then '0xbb4cdb9cbd36b01bd1cbaebf2de08d9173bc095c' when call:returnValues:symbol='WBTC' then '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' when call:returnValues:symbol='axlWBTC' then '0x2260fac5e5542a773aa44fbcfedf7c193bc2c599' when call:returnValues:symbol='axlFIL' then '0x0d8ce2a99bb6e3b7db580ed848240e4a0f9ae153' when call:returnValues:symbol='WFIL' then '0x0d8ce2a99bb6e3b7db580ed848240e4a0f9ae153' when call:returnValues:symbol='WFTM' then '0x4e15361fd6b4bb609fa63c81a2be19d873717870' when call:returnValues:symbol='axlFTM' then '0x4e15361fd6b4bb609fa63c81a2be19d873717870' when call:returnValues:symbol='WMATIC' then '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' when call:returnValues:symbol='axlMATIC' then '0x0d500b1d8e8ef31e21c99d1db9a6444d3adf1270' when call:returnValues:symbol='axlSOMM' then 'ibc/9BBA9A1C257E971E38C1422780CE6F0B0686F0A3085E2D61118D904BFE0F5F5E' when call:returnValues:symbol='axlATOM' then 'uatom' when call:returnValues:symbol='WGLMR' then '0xacc15dc74880c9944775448304b263d191c6077f' when call:returnValues:symbol='axlGLMR' then '0xacc15dc74880c9944775448304b263d191c6077f' -- when call:returnValues:symbol='WMAI' then '0xa3fa99a148fa48d14ed51d610c367c61876997f1' when call:returnValues:symbol='axlWMAI' then '0xa3fa99a148fa48d14ed51d610c367c61876997f1' end as token_address from axelar.axelscan.fact_gmp where (created_at::date between '{{Start_Date}}' and '{{End_Date}}') and (call:transaction:to='0xce16f69375520ab01377ce7b88f5ba8c48f8d666' or call:returnValues:destinationContractAddress='0xce16f69375520ab01377ce7b88f5ba8c48f8d666')), tab2 as (select hour as date, avg(price) as avg_price, token_address from crosschain.price.ez_prices_hourly where hour::date between '{{Start_Date}}' and '{{End_Date}}' group by 1,3) select tab1.date as date, amount, amount*avg_price as amount_usd, symbol, tab1.token_address as token_address, source_chain, destination_chain, id, user from tab1 left join tab2 on tab1.date=tab2.date and tab1.token_address=tab2.token_address order by 1) select user, sum(amount_usd), case when sum(amount_usd)<=1 then 'V<=1' when sum(amount_usd)>1 and sum(amount_usd)<=10 then '110 and sum(amount_usd)<=100 then '10100 and sum(amount_usd)<=1000 then '1001000 and sum(amount_usd)<=10000 then '1k10000 and sum(amount_usd)<=100000 then '10k100000 and sum(amount_usd)<=1000000 then '100k1000000 then 'V>1M$' end as "Class" from tab3 where amount_usd is not null group by 1) select "Class", count(distinct user) as "User Count" from tab4 group by 1

🟡Squid Router