Query editor
-- Whale Wallet Profit Tracker for Dune Analytics (Fixed for DuneSQL / Trino)-- -------------------------------------------------------------- PERBAIKAN V3: Memperbaiki logika Realized PnL agar tidak bernilai 0.-- Volume beli dan jual kini dipisah per token sebelum dihitung selisihnya.-- ------------------------------------------------------------WITH-- ------------------------------------------------------------------ 1. Ambil Trader Aktif (Fokus pada dompet yang aktif di DEX 30 hari terakhir)-- ----------------------------------------------------------------active_traders AS (SELECTtx_from AS wallet,SUM(amount_usd) AS volume_30dFROM dex.tradesWHERE block_time >= NOW() - INTERVAL '30' DAYAND blockchain = 'ethereum'GROUP BY tx_from),-- ------------------------------------------------------------------ 2. Hitung Saldo USDC untuk Kandidat Whale-- ----------------------------------------------------------------usdc_transfers AS (SELECTt."to" AS wallet,CAST(t.value AS DOUBLE) AS amountFROM erc20_ethereum.evt_Transfer tJOIN active_traders a ON t."to" = a.walletWHERE t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48UNION ALLSELECTt."from" AS wallet,-CAST(t.value AS DOUBLE) AS amountFROM erc20_ethereum.evt_Transfer tJOIN active_traders a ON t."from" = a.walletWHERE t.contract_address = 0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48),usdc_balances AS (SELECTwallet,SUM(amount) / 1e6 AS usdc_balanceFROM usdc_transfersGROUP BY wallet),-- ------------------------------------------------------------------ 3. Kualifikasi Whale (DEX Volume >= $5M ATAU Saldo USDC >= $10k)-- ----------------------------------------------------------------whale_candidates AS (SELECTa.walletFROM active_traders aLEFT JOIN usdc_balances u ON a.wallet = u.walletWHERE a.volume_30d >= 5000000OR COALESCE(u.usdc_balance, 0) >= 10000),