SELECT DISTINCT

e.BLOCK_NUMBER,

e.BLOCK_TIMESTAMP,

e.TX_HASH,

LOWER(e.ORIGIN_FROM_ADDRESS) AS eligible_address,

b.current_bal AS arkm_balance, -- Using decimal-adjusted balance

b.decimals,

b.symbol,

p.price AS arkm_price,

(b.current_bal * p.price) AS value_usd

FROM

ethereum.core.fact_event_logs e

JOIN

ethereum.core.ez_current_balances b

ON LOWER(e.ORIGIN_FROM_ADDRESS) = LOWER(b.user_address)

AND LOWER(b.contract_address) = LOWER('0x6E2a43be0B1d33b726f0CA3b8de60b3482b8b050') -- ARKM token

LEFT JOIN

ethereum.price.ez_prices_hourly p

ON LOWER(b.contract_address) = LOWER(p.token_address)

AND DATE_TRUNC('hour', CURRENT_TIMESTAMP) = p.hour -- Get current price

WHERE

-- Filter events from the Arkham Intelligence Airdrop Contract

e.CONTRACT_ADDRESS = LOWER('0xB0A5df30eC774dbAD8AA3D463179c277745db199')

AND b.current_bal > 0

AND e.BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_TIMESTAMP)

AND LOWER(e.ORIGIN_FROM_ADDRESS) = LOWER('{{eligible_address}}') -- Add parameter for specific address

SELECT

BLOCK_TIMESTAMP,

eligible_address,

arkm_balance,

decimals,

symbol,

arkm_price,

value_usd,