Ez Bridge Activity

-- Daily bridge volume by protocol
SELECT 
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(DISTINCT tx_hash) AS bridge_txns,
    SUM(amount_usd) AS volume_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd IS NOT NULL
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

-- Top bridge routes (source to destination chains)
SELECT 
    blockchain AS source_chain,
    destination_chain,
    platform,
    COUNT(*) AS transfer_count,
    SUM(amount_usd) AS total_volume_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 7
    AND destination_chain IS NOT NULL
GROUP BY 1, 2, 3
ORDER BY 5 DESC
LIMIT 20;

-- User bridge activity analysis
SELECT 
    sender,
    COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS active_days,
    COUNT(DISTINCT platform) AS protocols_used,
    COUNT(DISTINCT destination_chain) AS chains_bridged_to,
    SUM(amount_usd) AS total_bridged_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 30
    AND amount_usd > 100  -- Filter small transfers
GROUP BY 1
HAVING COUNT(*) > 5  -- Active bridgers
ORDER BY 5 DESC
LIMIT 100;

-- Token flow analysis
SELECT 
    token_symbol,
    token_address,
    blockchain AS source_chain,
    destination_chain,
    COUNT(*) AS bridge_count,
    SUM(amount) AS total_amount,
    AVG(amount_usd) AS avg_transfer_usd
FROM <blockchain_name>.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 7
    AND token_symbol IS NOT NULL
GROUP BY 1, 2, 3, 4
HAVING COUNT(*) > 10
ORDER BY 5 DESC;

-- Bridge protocol comparison
WITH protocol_stats AS (
    SELECT 
        platform,
        COUNT(DISTINCT sender) AS unique_users,
        COUNT(*) AS total_transfers,
        AVG(amount_usd) AS avg_transfer_size,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount_usd) AS median_transfer_size,
        SUM(amount_usd) AS total_volume
    FROM <blockchain_name>.defi.ez_bridge_activity
    WHERE block_timestamp >= CURRENT_DATE - 30
        AND amount_usd IS NOT NULL
    GROUP BY 1
)
SELECT *
FROM protocol_stats
ORDER BY total_volume DESC;