Query editor
WITH rwa AS (SELECT DISTINCT LOWER(token_address) AS addr, chain_nameFROM dune.okxweb3wallet.result_token_list_summaryWHERE source = 'rwa'),bs_addr AS (SELECT DISTINCT '0x' || LOWER(to_hex(d.proxy)) AS addrFROM bstocks_bnb.securitiestokenfactory_evt_tokendeployed dWHERE UPPER(d.symbol) NOT LIKE 'TEST%'),r AS (SELECT CAST(date_trunc('day', block_time) AS date) AS day, chain,LOWER(token_bought_address) AS b, LOWER(token_sold_address) AS s,amount_usd, trader_address,CASE WHEN platform_type = 'B_End' THEN competitor || ' B'WHEN platform_type = 'C_End' THEN competitor || ' C'ELSE competitor END AS competitorFROM dune.okxweb3wallet.result_raw_data_summary_part_1WHERE block_time >= CAST(CURRENT_DATE - INTERVAL '30' DAY AS timestamp)UNION ALLSELECT CAST(date_trunc('day', block_time) AS date), chain,LOWER(token_bought_address), LOWER(token_sold_address),amount_usd, trader_address,CASE WHEN platform_type = 'B_End' THEN competitor || ' B'WHEN platform_type = 'C_End' THEN competitor || ' C'ELSE competitor ENDFROM dune.okxweb3wallet.result_raw_data_summary_part_2WHERE block_time >= CAST(CURRENT_DATE - INTERVAL '30' DAY AS timestamp)),-- 竞品表 RWA 交易;剔除 bStocks 代币交易(bStocks 全量另行计入 Binance Wallet)comp_rwa AS (SELECT r.day, r.competitor, r.amount_usd, r.trader_addressFROM rLEFT JOIN rwa rb ON rb.chain_name = r.chain AND rb.addr = r.bLEFT JOIN rwa rs ON rs.chain_name = r.chain AND rs.addr = r.sWHERE (rb.addr IS NOT NULL OR rs.addr IS NOT NULL)AND COALESCE(r.b, '') NOT IN (SELECT addr FROM bs_addr)AND COALESCE(r.s, '') NOT IN (SELECT addr FROM bs_addr)),-- bStocks 全量(transfers 口径,含 RFQ)→ 归入 Binance Walletbs_toks AS (SELECT d.proxy AS addrFROM bstocks_bnb.securitiestokenfactory_evt_tokendeployed dJOIN tokens.erc20 e ON e.blockchain = 'bnb' AND e.contract_address = d.proxyWHERE UPPER(d.symbol) NOT LIKE 'TEST%' AND e.symbol IS NOT NULL),bs_rows AS (SELECTblock_date AS day,'Binance Wallet' AS competitor,GREATEST(COALESCE(SUM(CASE WHEN token_bought_address IN (SELECT addr FROM bs_toks) THEN amount_usd ELSE 0 END), 0),COALESCE(SUM(CASE WHEN token_sold_address IN (SELECT addr FROM bs_toks) THEN amount_usd ELSE 0 END), 0)) AS amount_usd,