Ez Decoded Event Logs

SELECT 
    block_timestamp,
    tx_hash,
    contract_address,
    contract_name,
    event_name,
    decoded_log:from::string AS from_address,
    decoded_log:to::string AS to_address,
    decoded_log:value::numeric AS amount,
    -- Convert to decimal (assuming 18 decimals)
    decoded_log:value::numeric / POW(10, 18) AS amount_decimal
FROM crosschain.evm.ez_decoded_event_logs
WHERE contract_address = LOWER('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') -- USDC
    AND event_name = 'Transfer'
    AND block_timestamp >= CURRENT_DATE - 7
    AND decoded_log:value::numeric > 1000000000 -- Over 1000 USDC
ORDER BY block_timestamp DESC;

SELECT 
    block_timestamp,
    tx_hash,
    contract_address AS pool_address,
    event_name,
    decoded_log:sender::string AS sender,
    decoded_log:recipient::string AS recipient,
    decoded_log:amount0::numeric AS amount0,
    decoded_log:amount1::numeric AS amount1,
    decoded_log:sqrtPriceX96::numeric AS sqrt_price,
    decoded_log:liquidity::numeric AS liquidity,
    decoded_log:tick::integer AS tick
FROM crosschain.evm.ez_decoded_event_logs
WHERE event_name = 'Swap'
    AND contract_address IN (
        SELECT address FROM crosschain.evm.dim_contracts 
        WHERE contract_name ILIKE '%Uniswap V3%'
    )
    AND block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum'
LIMIT 100;

SELECT 
    block_timestamp,
    tx_hash,
    contract_address,
    contract_name AS collection_name,
    decoded_log:from::string AS from_address,
    decoded_log:to::string AS to_address,
    decoded_log:tokenId::string AS token_id
FROM crosschain.evm.ez_decoded_event_logs
WHERE event_name = 'Transfer'
    AND decoded_log:tokenId IS NOT NULL  -- Indicates ERC-721
    AND block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum'
ORDER BY block_timestamp DESC;

SELECT 
    DATE_TRUNC('day', block_timestamp) AS day,
    event_name,
    COUNT(*) AS event_count,
    COUNT(DISTINCT decoded_log:minter::string) AS unique_users
FROM crosschain.evm.ez_decoded_event_logs
WHERE contract_name ILIKE '%compound%'
    AND event_name IN ('Mint', 'Redeem', 'Borrow', 'RepayBorrow')
    AND block_timestamp >= CURRENT_DATE - 30
    AND blockchain = 'ethereum'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

SELECT 
    block_timestamp,
    tx_hash,
    event_name,
    decoded_log,
    ARRAY_SIZE(decoded_log:path) AS swap_hops,
    decoded_log:amountIn::numeric AS amount_in,
    decoded_log:amountOutMin::numeric AS min_amount_out
FROM crosschain.evm.ez_decoded_event_logs
WHERE contract_address = LOWER('0x1111111254fb6c44bAC0beD2854e76F90643097d') -- 1inch
    AND event_name ILIKE '%swap%'
    AND block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum';