Query editor
/* ========= ⑤【最终合并】将鲸鱼净流量和价格数据通过日期连接起来 ========= *//* ========= ① 抓“鲸鱼 / rich / top holder” 地址 =========== */WITH whales AS (SELECT DISTINCTaddressFROM labels.addressesWHERELOWER(COALESCE(label_type, '')) LIKE '%whale%'OR LOWER(COALESCE(label_type, '')) LIKE '%rich%'OR LOWER(COALESCE(name, '')) LIKE '%whale%'OR LOWER(COALESCE(name, '')) LIKE '%rich%'), tx90 AS (SELECTblock_time,"from","to",value / 1e18 AS ethFROM ethereum.transactionsWHEREblock_time >= CURRENT_TIMESTAMP - INTERVAL '90' dayAND value > 0AND ("from" IN (SELECTaddressFROM whales)OR "to" IN (SELECTaddressFROM whales))), whale_flow_daily AS (SELECTDATE_TRUNC('day', t.block_time) AS day,SUM(CASEWHEN NOT w_to.address IS NULL AND w_from.address IS NULLTHEN t.ethWHEN NOT w_from.address IS NULL AND w_to.address IS NULLTHEN -t.ethELSE 0END) AS whale_net_flowFROM tx90 AS tLEFT JOIN whales AS w_fromON t."from" = w_from.addressLEFT JOIN whales AS w_toON t."to" = w_to.addressGROUP BY1), eth_prices AS (SELECTDATE_TRUNC('day', minute) AS day,AVG(price) AS eth_price_usdFROM prices.usdWHEREcontract_address = 0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2 /* 这是 WETH 的地址, 用来代表 ETH 价格 */AND minute >= CURRENT_TIMESTAMP - INTERVAL '90' day