Query editor
WITH marketplaces AS (SELECT *FROM (VALUES('OTC ELF @GiftElfRobot', UPPER('0:77624ec871b5b041367733cbce52c38263ba8eda3d9249183dfde0432a5f7761')),('TONNEL Marketplace @Tonnel_Network_bot', UPPER('0:59b357b68790b09b143198024b18da843dc43d894dc10838f4352cdbc138866b')),('TONNEL Marketplace Withdrawal @Tonnel_Network_bot', UPPER('0:ee59aff2efcd2ba6211ead3bf9259b41abcb24a2f5a9d0d3cb1ed2558b7441e3')),('Nest Store @nestore_robot', UPPER('0:835e549f3148722eed4a5e4526207d9c76bee38578b742c1d61a1245e68966fe')),('WTS @wtstonbot', UPPER('0:2f110ae2feb3ca85cba7a088e03864776968486fcb7b8e338223b57ee0d6c846')),('Giftpot @giftpot_bot', UPPER('0:4a65b3ab29951a2af941c2bff1cf5c023f156cb4b33e59e5c437342e287af435')),('GiftON Market @gifton_market_bot', UPPER('0:3a127974baf796039db8bf5c55feecf808e2622d439b866ef5ec0848075e77ac'))) AS t(marketplace_name, marketplace_address)),deduped_jetton_events AS (SELECT DISTINCT tx_hash, source, destination, amountFROM ton.jetton_eventsWHERE block_date > CAST('2025-01-01' AS DATE)AND jetton_master = UPPER('0:b113a994b5024a16719f69139328eb759596c38a25f59028b146fecdc3621dfe')),jetton_flows AS (-- Case 1: user sends jettons TO a marketplaceSELECTsource AS wallet,destination AS marketplace_address,amountFROM deduped_jetton_eventsJOIN marketplaces ON destination = marketplace_addressUNION ALL-- Case 2: user receives jettons FROM a marketplaceSELECTdestination AS wallet,source AS marketplace_address,amountFROM deduped_jetton_eventsJOIN marketplaces ON source = marketplace_address)SELECTjf.wallet,m.marketplace_name,SUM(jf.amount) / 1e6 AS total_usdtFROM jetton_flows jfJOIN marketplaces m ON jf.marketplace_address = m.marketplace_addressGROUP BY jf.wallet, m.marketplace_nameORDER BY total_usdt DESC;