Query editor
WITH claim_events AS (SELECT DISTINCT "to" AS walletFROM erc20_bnb.evt_transferWHERE "from" = 0x56aa6D651bfefA9207B35E508716466359BAe8eFAND contract_address = 0x66fd8de541c0594b4dccdfc13bf3a390e50d3afd),holders AS (SELECT"to" AS wallet,SUM(value) AS balance_rawFROM erc20_bnb.evt_transferWHERE contract_address = 0x66fd8de541c0594b4dccdfc13bf3a390e50d3afdGROUP BY "to"),turtle_price AS (SELECT MAX(price) AS price_usdFROM prices.latestWHERE contract_address = 0x66fd8de541c0594b4dccdfc13bf3a390e50d3afd)SELECTMAX(tp.price_usd) AS token_price_usd,ROUND(SUM(h.balance_raw)/1e18 * MAX(tp.price_usd), 2) AS total_airdrop_value_usd,ROUND(SUM(CASE WHEN c.wallet IS NOT NULL THEN h.balance_raw ELSE 0 END)/1e18 * MAX(tp.price_usd), 2) AS claimed_airdrop_value_usd,ROUND(SUM(CASE WHEN c.wallet IS NOT NULL THEN h.balance_raw ELSE 0 END)/1e18, 2) AS total_turtle_claimed,ROUND(SUM(CASE WHEN c.wallet IS NULL THEN h.balance_raw ELSE 0 END)/1e18, 2) AS total_turtle_left_to_claim,COUNT(DISTINCT CASE WHEN c.wallet IS NOT NULL THEN h.wallet END) AS wallets_claimed,COUNT(DISTINCT CASE WHEN c.wallet IS NULL THEN h.wallet END) AS wallets_left_to_claimFROM holders hLEFT JOIN claim_events c ON h.wallet = c.walletCROSS JOIN turtle_price tp;