Blockchain functions and data processing

parse(contract_address, topics, data) AS parser_result

-- Parsing ERC-20 transfer logs
SELECT 
    parse("0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2", trn.topics, trn.data) AS parser,
    trn.hash AS transaction_hash
FROM Ethereum.transactions.log(
    WHERE "to" = "0x7a250d5630b4cf539739df2c5dacb4c659f2488d" 
    LIMIT 1000
) AS trn;
 
-- Using the parse results
INSERT INTO Contract.erc_proxy(ref, dst, src, wad, hash)
SELECT 
    uuid(tb.hash) AS ref, 
    tb.parser.dst,      -- Recipient address  
    tb.parser.src,      -- Sender address
    tb.parser.wad,      -- Token amount
    tb.hash
FROM (
    SELECT 
        parse("0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2", trn.topics, trn.data) AS parser,
        trn.hash
    FROM Ethereum.transactions.log(
        WHERE "to" = "0x7a250d5630b4cf539739df2c5dacb4c659f2488d" 
        LIMIT 1000
    ) AS trn
    LIMIT 1000 TYPE LIST
) AS tb
TYPE LIST;

-- Basic log query
SELECT 
    "address",
    "topics", 
    "data",
    "blockNumber",
    "transactionHash",
    "logIndex"
FROM Ethereum.transactions.log(
    WHERE "address" = &contract_address
    ORDER BY "blockNumber" DESC
    LIMIT 100
) AS logs
TYPE LIST;
 
-- Filtering by transaction recipient
SELECT 
    "address" AS contract_address,
    "topics",
    "data", 
    "transactionHash"
FROM Ethereum.transactions.log(
    WHERE "to" = "0x7a250d5630b4cf539739df2c5dacb4c659f2488d"
    AND "blockNumber" > &start_block
    LIMIT 1000
) AS uniswap_logs
TYPE LIST;
 
-- Combined conditions
SELECT *
FROM Ethereum.transactions.log(
    WHERE "address" = &token_contract
    AND "blockNumber" BETWEEN &start_block AND &end_block
    AND "topics"[0] = &transfer_signature
    ORDER BY "blockNumber" ASC
    LIMIT 500
) AS filtered_logs
TYPE LIST;

-- Parameters in the WHERE section
FROM Ethereum.transactions.log(
    WHERE "address" IN (&contracts_list)
    AND "blockNumber" > &from_block  
    AND "value" > 0
    ORDER BY "blockNumber" DESC
    LIMIT &max_results
)
 
-- Complex filtering
FROM Ethereum.transactions.data(
    WHERE ("from" = &sender_address OR "to" = &receiver_address)
    AND "gasPrice" < &max_gas_price
    AND "timestamp" > &start_time
    ORDER BY "timestamp" DESC
    LIMIT 1000
)

-- Query to the Uniswap V2 Factory contract
SELECT 
    uv.pair,
    uv.tokenA, 
    uv.tokenB,
    uv.timestamp,
    uv.block,
    mt.name AS method_name
FROM Contract.uniswapv2factory AS uv
INNER JOIN Dictionary.contractsmethods AS mt 
    ON mt.id = uv.method_id
WHERE uv.pair IN (&pair::HEX) 
  AND uv.pair = &target_pair_id
TYPE LIST;
 
-- Comprehensive query with additional data
SELECT 
    uv.pair,
    uv.tokenA,
    uv.tokenB, 
    uv.timestamp,
    (SELECT &params.ref AS ref TYPE OBJECT) AS metadata,
    uv.block,
    mt.name AS method_name,
    mt1.id AS method_id,
    123 AS static_value,
    (SELECT 456 AS id TYPE OBJECT) AS nested_data,
    &params.ref AS reference
FROM Contract.uniswapv2factory AS uv
INNER JOIN Dictionary.contractsmethods AS mt 
    ON mt.id = uv.method_id
WHERE uv.pair IN (&pair::HEX) 
  AND uv.pair = 1000
TYPE LIST;

-- Creating a table for ERC-20 events
CREATE DICTIONARY IF NOT EXISTS erc20_transfers (
    ref UUID field DEFAULT UUID() NOT NULL,
    token_contract TEXT field NOT NULL,
    from_address TEXT field NOT NULL,
    to_address TEXT field NOT NULL,
    amount DECIMAL(38,18) field NOT NULL,
    block_number INTEGER field NOT NULL,
    transaction_hash TEXT field NOT NULL,
    log_index INTEGER field NOT NULL,
    PRIMARY KEY (ref)
);
 
-- Parsing and storing ERC-20 transfers
INSERT INTO dictionary.erc20_transfers (
    token_contract, from_address, to_address, amount,
    block_number, transaction_hash, log_index
)
SELECT 
    logs."address" AS token_contract,
    parsed.from_address,
    parsed.to_address,
    parsed.amount::DECIMAL / 1e18 AS amount, -- Conversion from wei
    logs."blockNumber"::INTEGER,
    logs."transactionHash",
    logs."logIndex"::INTEGER
FROM (
    SELECT 
        "address", "topics", "data", "blockNumber", 
        "transactionHash", "logIndex",
        parse("address", "topics", "data") AS parsed
    FROM Ethereum.transactions.log(
        WHERE "topics"[0] = '0xddf252ad1be2c89b69c2b068fc378daa952ba7f163c4a11628f55a4df523b3ef' -- Transfer event signature
        AND "blockNumber" > &last_processed_block
        LIMIT 1000
    )
) AS logs
WHERE parsed.from_address IS NOT NULL 
  AND parsed.to_address IS NOT NULL;

-- Creating unique references based on hashes
INSERT INTO Contract.transaction_events (ref, hash, contract_address, event_data)
SELECT 
    UUID(trn.hash) AS ref,              -- Deterministic UUID
    trn.hash,
    trn."address" AS contract_address,
    parse(trn."address", trn.topics, trn.data) AS event_data
FROM Ethereum.transactions.log(
    WHERE "blockNumber" > &start_block
    LIMIT 500
) AS trn;
 
-- Checking for duplicates by hash
INSERT INTO dictionary.unique_transactions (ref, hash, processed_at)
SELECT 
    UUID(hash) AS ref,
    hash,
    NOW() AS processed_at
FROM (
    SELECT DISTINCT "transactionHash" AS hash
    FROM Ethereum.transactions.log(
        WHERE "blockNumber" BETWEEN &start AND &end
    )
) AS unique_hashes
WHERE NOT EXISTS (
    SELECT 1 FROM dictionary.unique_transactions ut 
    WHERE ut.ref = UUID(unique_hashes.hash)
);

-- Analyzing trading activity on DEXes.
-- Ineron SQL has no CTEs — aggregate directly over a subquery in FROM.
SELECT 
    dex_activity.dex_contract,
    COUNT(*) AS trade_count,
    SUM(dex_activity.eth_amount) AS total_eth_volume,
    AVG(dex_activity.eth_amount) AS avg_trade_size,
    SUM(dex_activity.fee_paid) AS total_fees_paid
FROM (
    SELECT 
        trn."from" AS trader,
        trn."to" AS dex_contract,
        trn."value"::NUMBER / 1e18 AS eth_amount,
        trn."gasUsed"::NUMBER * trn."gasPrice"::NUMBER / 1e18 AS fee_paid,
        trn."blockNumber"::INTEGER AS block_number,
        trn."timestamp"::INTEGER AS trade_time
    FROM Ethereum.transactions.data(
        WHERE "to" IN (
            '0x7a250d5630b4cf539739df2c5dacb4c659f2488d',  -- Uniswap V2
            '0xe592427a0aece92de3edee1f18e0157c05861564',  -- Uniswap V3
            '0xd9e1ce17f2641f24ae83637ab66a2cca9c378b9f'   -- SushiSwap
        )
        AND "value" > 0
        AND "timestamp" > &day_ago
        ORDER BY "timestamp" DESC
        LIMIT 1000
    ) AS trn
) AS dex_activity
GROUP BY dex_activity.dex_contract
ORDER BY total_eth_volume DESC
TYPE LIST;

-- Tracking whale activity
SELECT 
    tx."from" AS whale_address,
    tx."to" AS recipient,
    tx."value"::NUMBER / 1e18 AS eth_amount,
    tx."blockNumber"::INTEGER AS block,
    tx."timestamp"::INTEGER AS when_transferred,
    
    -- Attempt to identify the recipient
    CASE 
        WHEN tx."to" IN (
            '0x7a250d5630b4cf539739df2c5dacb4c659f2488d',
            '0xe592427a0aece92de3edee1f18e0157c05861564'
        ) THEN 'DEX'
        WHEN tx."to" IN (
            '0xa0b86a33e6e7b9e0f7be03c19c22e5b6a7e11a23',
            '0x3d7a2f7ec2b8b7e8a8f5a5a5a5a5a5a5a5a5a5a5'
        ) THEN 'Exchange'
        ELSE 'Unknown'
    END AS recipient_type
FROM Ethereum.transactions.data(
    WHERE "value"::NUMBER > &whale_threshold  -- For example, 100 ETH
    AND "timestamp" > &recent_time
    ORDER BY "value"::NUMBER DESC
    LIMIT 100
) AS tx
TYPE LIST;

-- Processing new blocks via a webhook
INSERT INTO dictionary.block_events (
    block_number, block_hash, timestamp, 
    transaction_count, gas_used, processed_at
)
SELECT 
    &webhook.block.number::NUMBER AS block_number,
    &webhook.block.hash::TEXT AS block_hash,
    &webhook.block.timestamp::NUMBER AS timestamp,
    &webhook.block.transaction_count::NUMBER AS transaction_count,
    &webhook.block.gas_used::NUMBER AS gas_used,
    NOW() AS processed_at;
 
-- Notification about a new block
SELECT CALL(TG_SEND_MESSAGE, 
    '{"chat_id": ' || &monitoring_chat || 
    ', "text": "🔗 New block #' || &webhook.block.number::TEXT || 
    '\nTransactions: ' || &webhook.block.transaction_count::TEXT ||
    '\nGas: ' || &webhook.block.gas_used::TEXT || '"}') AS notification_sent;

-- Updating contract data on new events (no CTE — subquery in FROM)
INSERT INTO dictionary.contract_events (
    contract_address, event_type, event_data, 
    block_number, transaction_hash, processed_at
)
SELECT 
    new_events.contract_addr,
    'TokenTransfer' AS event_type,
    new_events.event_data::TEXT AS event_data,
    new_events.block_num,
    new_events.tx_hash,
    NOW() AS processed_at
FROM (
    SELECT 
        "address" AS contract_addr,
        parse("address", topics, data) AS event_data,
        "blockNumber"::INTEGER AS block_num,
        "transactionHash" AS tx_hash
    FROM Ethereum.transactions.log(
        WHERE "blockNumber" > (
            SELECT MAX(last_block) 
            FROM dictionary.contract_sync_status 
            WHERE contract_address = &monitored_contract
        )
        AND "address" = &monitored_contract
        ORDER BY "blockNumber" ASC
        LIMIT 1000
    )
) AS new_events
WHERE new_events.event_data IS NOT NULL;

-- ✅ Good: use LIMIT and sorting
FROM Ethereum.transactions.log(
    WHERE "blockNumber" BETWEEN &start_block AND &end_block
    ORDER BY "blockNumber" ASC, "logIndex" ASC  
    LIMIT 1000
)
 
-- ❌ Bad: overly large ranges without limits
FROM Ethereum.transactions.log(
    WHERE "blockNumber" > 0  -- May return millions of records
)

-- ✅ Good: cast types for numeric values
SELECT 
    "value"::NUMBER / 1e18 AS eth_amount,
    "gasPrice"::NUMBER AS gas_price,
    "blockNumber"::INTEGER AS block
FROM Ethereum.transactions.data();
 
-- ❌ Bad: using string representations
SELECT "value", "gasPrice", "blockNumber"  -- Strings, inconvenient for calculations
FROM Ethereum.transactions.data();

-- ✅ Good: validate parse results
SELECT 
    parser.dst,
    parser.src,
    parser.wad
FROM (
    SELECT parse(&contract, topics, data) AS parser
    FROM Ethereum.transactions.log(LIMIT 100)
) AS parsed
WHERE parser.dst IS NOT NULL 
  AND parser.src IS NOT NULL
  AND parser.wad IS NOT NULL;