Blockchain integration

SELECT columns 
FROM CRYPTONET.TRANSACTIONS.source(parameters) 
WHERE conditions

-- Basic Ethereum transaction query
SELECT "hash", "blockNumber", "from", "to", "value"
FROM ETHEREUM.TRANSACTIONS.DATA()
LIMIT 100 TYPE LIST;
 
-- Query with filtering by address
SELECT "hash", "timestamp", "gasUsed", "value"
FROM ETHEREUM.TRANSACTIONS.DATA(WHERE "to" = &target_address LIMIT 50)
TYPE LIST;
 
-- Querying transaction logs
SELECT "address", "topics", "data", "logIndex"
FROM ETHEREUM.TRANSACTIONS.LOGS(WHERE "address" = &contract_address)
TYPE LIST;

-- WHERE - filtering by conditions
FROM ETHEREUM.TRANSACTIONS.DATA(WHERE "blockNumber" > &start_block)
 
-- ORDER BY - sorting the results  
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "to" = &address 
    ORDER BY "blockNumber" DESC
)
 
-- LIMIT - limiting the number of results
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "from" = &sender
    LIMIT 1000
)
 
-- OFFSET - offset for pagination
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "value" > 0
    ORDER BY "timestamp" DESC  
    LIMIT 100 OFFSET 500
)
 
-- Combined parameters
FROM ETHEREUM.TRANSACTIONS.LOGS(
    WHERE "address" = &contract_addr AND "blockNumber" > &start_block
    ORDER BY "blockNumber" ASC
    LIMIT 100 OFFSET 200
)

-- Creating a CONTRACT object for tracking
CREATE DICTIONARY IF NOT EXISTS my_contracts (
    ref UUID DEFAULT UUID() NOT NULL,
    contract_address TEXT NOT NULL,
    abi_hash TEXT,
    deployment_block INTEGER,
    PRIMARY KEY (ref)
);
 
-- Querying contract events
SELECT event_name, block_number, transaction_hash, event_data
FROM CONTRACT.events 
WHERE contract_address = &my_contract
  AND event_signature = &transfer_topic
ORDER BY block_number DESC
LIMIT 100 TYPE LIST;

-- Transfer events for ERC-20 tokens
SELECT "from", "to", "value", "blockNumber"
FROM CONTRACT.events(
    WHERE "address" = &token_contract 
      AND "topics"[0] = 'Transfer'
    ORDER BY "blockNumber" DESC
    LIMIT 1000
) AS transfers
TYPE LIST;
 
-- Events of a specific contract over a period
SELECT "topics", "data", "blockNumber", "transactionHash"
FROM CONTRACT.events(
    WHERE "address" = &contract_address 
      AND "blockNumber" BETWEEN &start_block AND &end_block
    ORDER BY "blockNumber" ASC
) AS contract_events
TYPE LIST;

-- Creating a register to track balances
CREATE LEDGER IF NOT EXISTS token_balances (
    ref UUID DEFAULT UUID() NOT NULL,
    account TEXT NOT NULL,
    token_contract TEXT NOT NULL,
    balance RESOURCE DECIMAL(38,18) DEFAULT 0,
    last_update_block INTEGER,
    PRIMARY KEY (ref)
);
 
-- Querying the register with parameters
SELECT account, token_contract, balance, last_update_block
FROM LEDGER.token_balances(
    WHERE account = &user_address 
      AND balance > 0
    ORDER BY balance DESC
    LIMIT 50
) AS user_tokens
TYPE LIST;

-- Total balances by token
SELECT token_contract, 
       COUNT(account) as holders_count,
       SUM(balance) as total_supply,
       AVG(balance) as avg_balance
FROM LEDGER.token_balances(
    WHERE balance > 0
) AS balances
GROUP BY token_contract
ORDER BY total_supply DESC
TYPE LIST;
 
-- History of balance changes
SELECT account, 
       balance_before, 
       balance_after,
       (balance_after - balance_before) as balance_change,
       block_number,
       transaction_hash
FROM LEDGER.balance_changes(
    WHERE account = &target_account 
      AND block_number > &start_block
    ORDER BY block_number DESC
    LIMIT 100
) AS changes
TYPE LIST;

-- Analyzing DEX trading activity
SELECT 
    "from" as trader,
    "to" as dex_contract,
    value::NUMBER / 1e18 as eth_amount,
    "gasUsed"::NUMBER * "gasPrice"::NUMBER / 1e18 as fee_paid,
    "blockNumber",
    "timestamp"
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "to" IN (&uniswap_v2, &uniswap_v3, &sushiswap)
      AND "value" > 0
      AND "timestamp" > &day_ago
    ORDER BY "timestamp" DESC
    LIMIT 500
) AS dex_trades
TYPE LIST;
 
-- Monitoring large transfers
SELECT 
    tx."from" as sender,
    tx."to" as recipient, 
    tx."value"::NUMBER / 1000000000000000000 as eth_amount,
    tx."timestamp",
    logs."address" as token_contract,
    logs."data" as token_amount_hex
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "value" > &min_eth_amount
) AS tx
JOIN ETHEREUM.TRANSACTIONS.LOGS(
    WHERE "topics" LIKE &transfer_signature || '%'
) AS logs ON tx."hash" = logs."transactionHash"
WHERE tx."timestamp" > &start_time
ORDER BY eth_amount DESC
LIMIT 100 TYPE LIST;

-- Ethereum activity
SELECT 
    'Ethereum' as network,
    COUNT(*) as tx_count,
    SUM("gasUsed"::NUMBER) as total_gas,
    AVG("gasPrice"::NUMBER) as avg_gas_price
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "timestamp" > &day_ago
)
TYPE OBJECT;

-- BSC activity
SELECT 
    'BSC' as network,
    COUNT(*) as tx_count,
    SUM("gasUsed"::NUMBER) as total_gas,
    AVG("gasPrice"::NUMBER) as avg_gas_price  
FROM BSC.TRANSACTIONS.DATA(
    WHERE "timestamp" > &day_ago
)
TYPE OBJECT;

-- Polygon activity
SELECT 
    'Polygon' as network,
    COUNT(*) as tx_count,
    SUM("gasUsed"::NUMBER) as total_gas,
    AVG("gasPrice"::NUMBER) as avg_gas_price
FROM MATIC.TRANSACTIONS.DATA(
    WHERE "timestamp" > &day_ago
)
TYPE OBJECT;

-- GET endpoint: /api/wallet/balance
SELECT 
    &wallet_address as address,
    incoming.total_received - outgoing.total_spent as eth_balance,
    balances.token_count as token_balances,
    activity.last_tx_time as last_transaction_time
FROM (
    SELECT ISNULL(SUM("value"::NUMBER), 0) / 1000000000000000000 as total_received
    FROM ETHEREUM.TRANSACTIONS.DATA(
        WHERE "to" = &wallet_address
    )
) AS incoming,
(
    SELECT ISNULL(SUM("value"::NUMBER + "gasUsed"::NUMBER * "gasPrice"::NUMBER), 0) / 1000000000000000000 as total_spent
    FROM ETHEREUM.TRANSACTIONS.DATA(
        WHERE "from" = &wallet_address  
    )
) AS outgoing,
(
    SELECT FCOUNT(*) as token_count
    FROM LEDGER.token_balances(
        WHERE account = &wallet_address AND balance > 0
    )
) AS balances,
(
    SELECT MAX("timestamp") as last_tx_time
    FROM ETHEREUM.TRANSACTIONS.DATA(
        WHERE "from" = &wallet_address OR "to" = &wallet_address
    )
) AS activity
TYPE OBJECT;

-- GET endpoint: /api/contract/events
SELECT 
    "blockNumber",
    "transactionHash", 
    "logIndex",
    "topics",
    "data",
    "timestamp"
FROM CONTRACT.events(
    WHERE "address" = &contract_address
      AND "blockNumber" > ISNULL(&from_block, 0)
      AND (&topic0 IS NULL OR "topics"[0] = &topic0)
    ORDER BY "blockNumber" DESC, "logIndex" DESC
    LIMIT ISNULL(&limit, 100)
) AS events
TYPE LIST;

-- Creating indexed views for frequent queries
CREATE DICTIONARY IF NOT EXISTS indexed_transfers (
    ref UUID DEFAULT UUID() NOT NULL,
    transaction_hash TEXT NOT NULL,
    block_number INTEGER NOT NULL,
    from_address TEXT,
    to_address TEXT,
    token_address TEXT,
    amount DECIMAL(38,18),
    timestamp INTEGER,
    PRIMARY KEY (ref)
);

-- Efficient pagination for large data sets
SELECT 
    "blockNumber",
    "transactionIndex", 
    "hash",
    "from",
    "to",
    "value"
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "blockNumber" BETWEEN &start_block AND &end_block
    ORDER BY "blockNumber" ASC, "transactionIndex" ASC
    LIMIT 1000 OFFSET &offset
) AS paginated_txs
TYPE LIST;

-- Checking the latest block
SELECT 
    MAX("blockNumber") as latest_block,
    MAX("timestamp") as latest_timestamp,
    COUNT(*) as recent_tx_count
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "timestamp" > (&current_time - 3600)
)
TYPE OBJECT;

-- Blockchain query statistics
SELECT 
    'ethereum' as network,
    COUNT(*) as query_count,
    AVG("gasUsed"::NUMBER) as avg_gas,
    MAX("blockNumber") as max_block
FROM ETHEREUM.TRANSACTIONS.DATA(
    WHERE "timestamp" > &start_time
)
TYPE OBJECT;