with table1 as (with tab1 as (select date_trunc('day',block_timestamp) as day, origin_from_address as swapper,contract_address, sum(raw_amount) as vol, case when contract_address='0xff970a61a04b1ca14834a43f5de4533ebddb5cc8' then 'USDC' when contract_address='0xfea7a6a0b346362bf88a9e4a88416b77a57d6c2a' then 'MIM' when contract_address='0xda10009cbd5d07dd0cecc66161fc93d7c9000da1' then 'DAI' when contract_address='0xf97f4df75117a78c1a5a0dbb814af92458539fb4' then 'LINK' when contract_address='0x82af49447d8a07e3bd95bd0d56f35241523fbab1' then 'WETH' when contract_address='0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f' then 'WBTC' when contract_address='0x17fc002b466eec40dae837fc4be5c67993ddbd6f' then 'FRAX' when contract_address='0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9' then 'USDT' when contract_address='0xfa7f8980b0f1e64a2062791cc3b0871572f1f7f0' then 'UNI' end as symbol, case when (contract_address='0xff970a61a04b1ca14834a43f5de4533ebddb5cc8' or contract_address='0xfd086bc7cd5c481dcc9c85ebe478a1c0b69fcbb9') then vol/pow(10,6) when contract_address='0x2f2a2543b76a4166549f7aab2e75bef0aefc5b0f' then vol/pow(10,8) else vol/pow(10,18) end as volume from arbitrum.core.fact_token_transfers where to_address='0x489ee077994b6658eafa855c308275ead8097c4a' and contract_address<>'0x45096e7aa921f27590f8f19e457794eb09678141' group by 1,2,3 order by 1), tab2 as (select date_trunc('day',hour) as day, avg(price) as avg_price, symbol from ethereum.core.fact_hourly_token_prices group by 1,3) select swapper, sum(volume*avg_price) as Swap_from_Volume, case when Swap_from_Volume<=10 then 'Vol<=10' when Swap_from_Volume>10 and Swap_from_Volume<=100 then '10100 and Swap_from_Volume<=1000 then '1001000 and Swap_from_Volume<=10000 then '1,00010000 and Swap_from_Volume<=100000 then '10,000100000 and Swap_from_Volume<=1000000 then '100,0001000000 then 'Vol>1,000,000' end as group_ from tab1 left join tab2 on tab1.day=tab2.day and tab1.symbol=tab2.symbol where swapper<>'0xef9092d35fda3e5b6e2dd3fac5b580aefc346faf' and swapper<>'0x7af4ba11ee55091f487d06977c1f4c3805e3ef48' and swapper<>'0xa73731077b511b39853fb149afec948d3db9ba71' and swapper<>'0xd4266f8f82f7405429ee18559e548979d49160f3' group by 1) select group_, count(swapper) as "Swapper Count" from table1 group by 1 order by 2

GMX Traders