Ez Asset Metadata

-- Find all verified USD stablecoins
SELECT 
    blockchain,
    token_address,
    name,
    symbol,
    decimals,
    is_verified
FROM <blockchain_name>.price.ez_asset_metadata
WHERE is_verified = TRUE
    AND (
        symbol IN ('USDC', 'USDT', 'DAI', 'BUSD')
        OR name ILIKE '%USD%'
        OR name ILIKE '%stablecoin%'
    )
    AND is_native = FALSE
ORDER BY blockchain, symbol;

-- Find same assets across multiple chains
WITH asset_presence AS (
    SELECT 
        symbol,
        name,
        COUNT(DISTINCT blockchain) AS chain_count,
        ARRAY_AGG(DISTINCT blockchain) AS chains,
        ARRAY_AGG(token_address) AS addresses
    FROM <blockchain_name>.price.ez_asset_metadata
    WHERE is_native = FALSE
        AND is_verified = TRUE
    GROUP BY 1, 2
)
SELECT * FROM asset_presence
WHERE chain_count > 3
ORDER BY chain_count DESC, symbol;

-- Get all native assets with metadata
SELECT 
    blockchain,
    symbol,
    name,
    decimals,
    CASE blockchain
        WHEN 'ethereum' THEN 'Proof of Stake'
        WHEN 'binance' THEN 'Proof of Staked Authority'
        WHEN 'polygon' THEN 'Proof of Stake'
        WHEN 'avalanche' THEN 'Avalanche Consensus'
        ELSE 'Various'
    END AS consensus_mechanism
FROM <blockchain_name>.price.ez_asset_metadata
WHERE is_native = TRUE
ORDER BY blockchain;