Ez Native Transfers

SELECT 
    DATE_TRUNC('day', block_timestamp) AS day,
    blockchain,
    native_token,
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    COUNT(DISTINCT to_address) AS unique_receivers,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    SUM(amount_usd) AS total_usd,
    MAX(amount_usd) AS largest_transfer_usd
FROM crosschain.evm.ez_native_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount > 0
GROUP BY 1, 2, 3
ORDER BY 1 DESC, 6 DESC;

SELECT 
    blockchain,
    native_token,
    block_timestamp,
    tx_hash,
    from_address,
    to_address,
    amount,
    amount_usd,
    origin_from_address,
    origin_to_address,
    trace_index
FROM crosschain.evm.ez_native_transfers
WHERE amount_usd > 1000000  -- Over $1M USD
    AND block_timestamp >= CURRENT_DATE - 7
ORDER BY amount_usd DESC
LIMIT 100;

WITH exchange_addresses AS (
    SELECT DISTINCT address 
    FROM crosschain.core.dim_labels 
    WHERE label_type = 'exchange'
)
SELECT 
    blockchain,
    native_token,
    DATE_TRUNC('hour', block_timestamp) AS hour,
    CASE 
        WHEN to_address IN (SELECT address FROM exchange_addresses) THEN 'Deposit'
        WHEN from_address IN (SELECT address FROM exchange_addresses) THEN 'Withdrawal'
    END AS transfer_type,
    COUNT(*) AS transfer_count,
    SUM(amount) AS total_amount,
    SUM(amount_usd) AS total_usd
FROM crosschain.evm.ez_native_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
    AND (to_address IN (SELECT address FROM exchange_addresses)
         OR from_address IN (SELECT address FROM exchange_addresses))
GROUP BY 1, 2, 3, 4
ORDER BY 1, 3 DESC;

-- Compare external vs internal transfers across networks
SELECT 
    blockchain,
    native_token,
    CASE 
        WHEN trace_index = 0 THEN 'External Transfer'
        ELSE 'Internal Transfer'
    END AS transfer_type,
    COUNT(*) AS count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount,
    SUM(amount_usd) AS total_volume_usd
FROM crosschain.evm.ez_native_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
GROUP BY 1, 2, 3
ORDER BY 1, 2, 4 DESC;

WITH contract_balances AS (
    SELECT 
        blockchain,
        to_address AS address,
        SUM(amount) AS inflows
    FROM crosschain.evm.ez_native_transfers
    WHERE to_address IN (SELECT address FROM crosschain.evm.dim_contracts)
    GROUP BY 1, 2
),
outflows AS (
    SELECT 
        blockchain,
        from_address AS address,
        SUM(amount) AS outflows
    FROM crosschain.evm.ez_native_transfers
    WHERE from_address IN (SELECT address FROM crosschain.evm.dim_contracts)
    GROUP BY 1, 2
)
SELECT 
    c.blockchain,
    c.address,
    dc.name AS contract_name,
    COALESCE(c.inflows, 0) - COALESCE(o.outflows, 0) AS net_balance,
    c.inflows,
    o.outflows
FROM contract_balances c
LEFT JOIN outflows o ON c.blockchain = o.blockchain AND c.address = o.address
LEFT JOIN crosschain.evm.dim_contracts dc ON c.address = dc.address
WHERE COALESCE(c.inflows, 0) - COALESCE(o.outflows, 0) > 100  -- Over 100 native tokens
ORDER BY c.blockchain, net_balance DESC
LIMIT 50;

SELECT 
    blockchain,
    native_token,
    DATE_TRUNC('day', block_timestamp) AS day,
    COUNT(*) AS daily_transfers,
    SUM(amount) AS daily_volume_native,
    SUM(amount_usd) AS daily_volume_usd,
    AVG(amount_usd) AS avg_transfer_usd,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount_usd) AS p95_transfer_usd
FROM crosschain.evm.ez_native_transfers
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount > 0
GROUP BY 1, 2, 3
ORDER BY 1, 2, 3 DESC;