Ez Token Transfers

SELECT 
    block_timestamp,
    tx_hash,
    symbol,
    from_address,
    to_address,
    amount,
    amount_usd,
    token_price,
    contract_address
FROM crosschain.evm.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 7
    AND amount_usd > 100000  -- Over $100k
    AND blockchain = 'ethereum'
ORDER BY amount_usd DESC
LIMIT 100;

SELECT 
    DATE_TRUNC('day', block_timestamp) AS day,
    symbol,
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd,
    AVG(amount_usd) AS avg_transfer_usd
FROM crosschain.evm.ez_token_transfers
WHERE symbol IN ('USDC', 'USDT', 'DAI', 'BUSD')
    AND block_timestamp >= CURRENT_DATE - 30
    AND blockchain = 'ethereum'
GROUP BY 1, 2
ORDER BY 1 DESC, 6 DESC;

WITH dex_addresses AS (
    SELECT address 
    FROM crosschain.core.dim_labels 
    WHERE label_type = 'dex' 
    AND label_subtype IN ('pool', 'router')
)
SELECT 
    DATE_TRUNC('hour', block_timestamp) AS hour,
    symbol,
    CASE 
        WHEN from_address IN (SELECT address FROM dex_addresses) THEN 'DEX Outflow'
        WHEN to_address IN (SELECT address FROM dex_addresses) THEN 'DEX Inflow'
    END AS flow_type,
    COUNT(*) AS transfers,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd
FROM crosschain.evm.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
    AND blockchain = 'ethereum'
    AND (from_address IN (SELECT address FROM dex_addresses)
         OR to_address IN (SELECT address FROM dex_addresses))
    AND symbol IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 6 DESC;

-- Most active token senders
SELECT 
    from_address,
    COUNT(DISTINCT contract_address) AS tokens_sent,
    COUNT(DISTINCT DATE(block_timestamp)) AS active_days,
    COUNT(*) AS total_transfers,
    SUM(amount_usd) AS total_usd_sent
FROM crosschain.evm.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
    AND blockchain = 'ethereum'
    AND amount_usd > 10  -- Filter dust
GROUP BY 1
HAVING COUNT(*) > 10
ORDER BY total_usd_sent DESC
LIMIT 100;

WITH first_transfers AS (
    SELECT 
        contract_address,
        symbol,
        MIN(block_timestamp) AS first_transfer,
        COUNT(*) AS transfer_count,
        COUNT(DISTINCT from_address) AS unique_senders,
        COUNT(DISTINCT to_address) AS unique_receivers
    FROM crosschain.evm.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 7
    AND blockchain = 'ethereum'
GROUP BY 1, 2
HAVING MIN(block_timestamp) >= CURRENT_DATE - 1
)
SELECT 
    ft.*,
    dc.name AS token_name,
    dc.decimals
FROM first_transfers ft
LEFT JOIN crosschain.evm.dim_contracts dc ON ft.contract_address = dc.address
ORDER BY transfer_count DESC;