Use Cases & Examples

-- See available databases
SHOW DATABASES LIKE '%ETHEREUM%';

-- See available schemas
USE DATABASE ETHEREUM_CORE;
SHOW SCHEMAS;

-- Check latest data
SELECT
    MAX(block_number) AS latest_block,
    MAX(block_timestamp) AS latest_timestamp
FROM ethereum.core.fact_blocks;

-- See columns in a table
DESCRIBE TABLE ethereum.core.fact_transactions;

-- Sample data
SELECT *
FROM ethereum.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1
LIMIT 10;

-- Transaction volume and unique addresses per day
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    COUNT(*) AS total_transactions,
    COUNT(DISTINCT from_address) AS unique_senders,
    COUNT(DISTINCT to_address) AS unique_receivers,
    SUM(eth_value) AS total_eth_transferred,
    AVG(gas_price / POWER(10, 9)) AS avg_gas_price_gwei
FROM ethereum.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 1;

-- Top gas consumers over the past week
SELECT
    from_address,
    COUNT(*) AS tx_count,
    SUM(gas_used) AS total_gas_used,
    SUM(gas_used * gas_price) / POWER(10, 18) AS total_eth_spent_on_gas
FROM ethereum.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 7
GROUP BY 1
ORDER BY total_gas_used DESC
LIMIT 100;

-- Analyze failed transactions
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    COUNT(*) AS total_txs,
    SUM(CASE WHEN success = FALSE THEN 1 ELSE 0 END) AS failed_txs,
    SUM(CASE WHEN success = FALSE THEN 1 ELSE 0 END)::FLOAT / COUNT(*) * 100 AS failure_rate_pct
FROM ethereum.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1
ORDER BY 1;

-- Top ERC-20 tokens by transfer volume (last 24h)
SELECT
    contract_address,
    symbol,
    COUNT(*) AS transfer_count,
    COUNT(DISTINCT from_address) AS unique_senders,
    COUNT(DISTINCT to_address) AS unique_receivers,
    SUM(amount_usd) AS total_volume_usd
FROM ethereum.core.ez_token_transfers
WHERE block_timestamp >= CURRENT_DATE - 1
  AND amount_usd > 0
GROUP BY 1, 2
ORDER BY total_volume_usd DESC
LIMIT 50;

-- Current top holders of USDC
SELECT
    address,
    current_balance,
    current_balance_usd
FROM ethereum.core.ez_current_balances
WHERE contract_address = LOWER('0xA0b86991c6218b36c1d19D4a2e9Eb0cE3606eB48') -- USDC
  AND current_balance > 0
ORDER BY current_balance DESC
LIMIT 100;

-- Addresses that deployed ERC-20 contracts this week
SELECT
    creator_address,
    COUNT(*) AS tokens_deployed,
    ARRAY_AGG(DISTINCT contract_address) AS deployed_contracts
FROM ethereum.core.dim_contracts
WHERE block_timestamp >= CURRENT_DATE - 7
  AND contract_name ILIKE '%token%'
GROUP BY 1
ORDER BY tokens_deployed DESC;

-- Large ETH transfers (>100 ETH)
SELECT
    block_timestamp,
    tx_hash,
    from_address,
    to_address,
    eth_value,
    eth_value * (SELECT price FROM ethereum.price.ez_prices_hourly WHERE symbol = 'ETH' ORDER BY hour DESC LIMIT 1) AS usd_value
FROM ethereum.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 7
  AND eth_value > 100
ORDER BY eth_value DESC
LIMIT 100;

-- Complete activity profile for an address
WITH address_txs AS (
    SELECT *
    FROM ethereum.core.fact_transactions
    WHERE block_timestamp >= CURRENT_DATE - 90
      AND (from_address = LOWER('0x...') OR to_address = LOWER('0x...'))
)
SELECT
    COUNT(*) AS total_transactions,
    MIN(block_timestamp) AS first_seen,
    MAX(block_timestamp) AS last_seen,
    COUNT(DISTINCT CASE WHEN from_address = LOWER('0x...') THEN tx_hash END) AS sent_count,
    COUNT(DISTINCT CASE WHEN to_address = LOWER('0x...') THEN tx_hash END) AS received_count,
    SUM(CASE WHEN from_address = LOWER('0x...') THEN eth_value ELSE 0 END) AS total_eth_sent,
    SUM(CASE WHEN to_address = LOWER('0x...') THEN eth_value ELSE 0 END) AS total_eth_received
FROM address_txs;

-- Daily DEX volumes by platform
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    platform,
    COUNT(*) AS swap_count,
    SUM(amount_in_usd) AS total_volume_usd,
    COUNT(DISTINCT trader) AS unique_traders
FROM ethereum.defi.ez_dex_swaps
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 1, 4 DESC;

-- Most active trading pairs on Uniswap v3
SELECT
    token_in,
    token_out,
    COUNT(*) AS swap_count,
    SUM(amount_in_usd) AS total_volume_usd,
    AVG(amount_in_usd) AS avg_swap_size_usd
FROM ethereum.defi.ez_dex_swaps
WHERE block_timestamp >= CURRENT_DATE - 7
  AND platform = 'uniswap-v3'
  AND amount_in_usd > 0
GROUP BY 1, 2
ORDER BY total_volume_usd DESC
LIMIT 50;

-- Aave deposits and borrows (last 7 days)
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    'Deposit' AS action_type,
    COUNT(*) AS action_count,
    SUM(amount_usd) AS total_usd
FROM ethereum.defi.ez_lending_deposits
WHERE block_timestamp >= CURRENT_DATE - 7
  AND platform = 'aave-v3'
GROUP BY 1, 2

UNION ALL

SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    'Borrow' AS action_type,
    COUNT(*) AS action_count,
    SUM(amount_usd) AS total_usd
FROM ethereum.defi.ez_lending_borrows
WHERE block_timestamp >= CURRENT_DATE - 7
  AND platform = 'aave-v3'
GROUP BY 1, 2

ORDER BY 1, 2;

-- Recent liquidations across lending protocols
SELECT
    block_timestamp,
    platform,
    liquidated_user,
    liquidator,
    collateral_token,
    debt_token,
    amount_usd
FROM ethereum.defi.ez_lending_liquidations
WHERE block_timestamp >= CURRENT_DATE - 1
ORDER BY amount_usd DESC
LIMIT 100;

-- Daily NFT sales by marketplace
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    marketplace,
    COUNT(*) AS sale_count,
    SUM(price_usd) AS total_volume_usd,
    AVG(price_usd) AS avg_sale_price_usd
FROM ethereum.nft.ez_nft_sales
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2
ORDER BY 1, 3 DESC;

-- Collections by sales volume (last 7 days)
SELECT
    nft_address,
    COUNT(*) AS sale_count,
    COUNT(DISTINCT buyer) AS unique_buyers,
    SUM(price_usd) AS total_volume_usd,
    MIN(price_usd) AS floor_price_usd,
    MAX(price_usd) AS highest_sale_usd
FROM ethereum.nft.ez_nft_sales
WHERE block_timestamp >= CURRENT_DATE - 7
GROUP BY 1
ORDER BY total_volume_usd DESC
LIMIT 50;

-- High-value NFT sales (greater than \$50K)
SELECT
    block_timestamp,
    marketplace,
    nft_address,
    token_id,
    seller,
    buyer,
    price_usd
FROM ethereum.nft.ez_nft_sales
WHERE block_timestamp >= CURRENT_DATE - 7
  AND price_usd > 50000
ORDER BY price_usd DESC;

-- Compare transaction activity across chains
SELECT
    'Ethereum' AS chain,
    COUNT(*) AS tx_count,
    COUNT(DISTINCT from_address) AS unique_addresses
FROM ETHEREUM_CORE.ethereum.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1

UNION ALL

SELECT
    'Arbitrum' AS chain,
    COUNT(*) AS tx_count,
    COUNT(DISTINCT from_address) AS unique_addresses
FROM ARBITRUM_CORE.arbitrum.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1

UNION ALL

SELECT
    'Solana' AS chain,
    COUNT(*) AS tx_count,
    COUNT(DISTINCT from_address) AS unique_signers
FROM SOLANA_CORE.solana.core.fact_transactions
WHERE block_timestamp >= CURRENT_DATE - 1;

-- Cross-chain bridge activity
SELECT
    DATE_TRUNC('day', block_timestamp) AS date,
    bridge_name,
    origin_chain,
    destination_chain,
    COUNT(*) AS bridge_count,
    SUM(amount_usd) AS total_volume_usd
FROM ethereum.defi.ez_bridge_activity
WHERE block_timestamp >= CURRENT_DATE - 30
GROUP BY 1, 2, 3, 4
ORDER BY 1, 6 DESC;

-- Hourly price data for major assets
SELECT
    hour,
    symbol,
    open,
    high,
    low,
    close,
    volume_24h
FROM ethereum.price.ez_prices_hourly
WHERE hour >= CURRENT_DATE - 7
  AND symbol IN ('ETH', 'WBTC', 'USDC', 'DAI')
ORDER BY hour DESC, symbol;

-- Calculate correlation between ETH and WBTC
WITH price_data AS (
    SELECT
        hour,
        symbol,
        close AS price
    FROM ethereum.price.ez_prices_hourly
    WHERE hour >= CURRENT_DATE - 30
      AND symbol IN ('ETH', 'WBTC')
)
SELECT
    CORR(
        MAX(CASE WHEN symbol = 'ETH' THEN price END),
        MAX(CASE WHEN symbol = 'WBTC' THEN price END)
    ) AS eth_wbtc_correlation
FROM price_data
GROUP BY hour
HAVING MAX(CASE WHEN symbol = 'ETH' THEN price END) IS NOT NULL
   AND MAX(CASE WHEN symbol = 'WBTC' THEN price END) IS NOT NULL;

-- Monitor data latency
SELECT
    'ethereum.core.fact_blocks' AS table_name,
    MAX(block_timestamp) AS latest_block_time,
    DATEDIFF('minute', MAX(block_timestamp), CURRENT_TIMESTAMP()) AS minutes_behind
FROM ethereum.core.fact_blocks

UNION ALL

SELECT
    'ethereum.defi.ez_dex_swaps' AS table_name,
    MAX(block_timestamp) AS latest_block_time,
    DATEDIFF('minute', MAX(block_timestamp), CURRENT_TIMESTAMP()) AS minutes_behind
FROM ethereum.defi.ez_dex_swaps;

-- Check for gaps in block data
WITH block_sequence AS (
    SELECT
        block_number,
        LAG(block_number) OVER (ORDER BY block_number) AS prev_block
    FROM ethereum.core.fact_blocks
    WHERE block_timestamp >= CURRENT_DATE - 1
)
SELECT
    block_number,
    prev_block,
    block_number - prev_block AS gap_size
FROM block_sequence
WHERE block_number - prev_block > 1
ORDER BY gap_size DESC;