crosscheck_neo4j_etl

1. Cross validate number of blocks
    i)BQ

    SELECT 
      COUNT(*) AS num_blocks
    FROM 
      `bigquery-public-data.crypto_bitcoin.blocks` AS blocks
    WHERE 
      blocks.number <= 300

    ii)Neo4j
    MATCH (b:Block) 
    RETURN 
        COUNT(b)

2. Cross validate number of transactions
    i)BQ

    SELECT 
      COUNT(*) AS num_transactions
    FROM 
      `bigquery-public-data.crypto_bitcoin.transactions` AS transactions
    WHERE 
      transactions.block_number <= 300


    ii)Neo4j
    MATCH (t:Transaction) 
    RETURN 
        COUNT(t)

3. Cross validate number of inputs
    i) BQ
    SELECT 
      COUNT(inputs)
    , SUM(inputs.value) AS num_inputs
    FROM 
      `bigquery-public-data.crypto_bitcoin.transactions` AS transactions,
      UNNEST(transactions.inputs) as inputs
    WHERE 
      transactions.block_number <= 300
        

    ii) Neo4j
    MATCH n=(output:Output)-[:sent]->(:Transaction) 
    RETURN 
        COUNT(output)
      , SUM(output.value)

4. Cross validate number of outputs    
    SELECT 
      COUNT(outputs)
    , SUM(outputs.value) AS num_inputs
    FROM 
      `bigquery-public-data.crypto_bitcoin.transactions` AS transactions,
      UNNEST(transactions.outputs) AS outputs
    WHERE 
      transactions.block_number <= 300

    ii) Neo4j
    MATCH n=(output:Output)<-[:received]-(:Transaction) 
    RETURN 
        COUNT(output)
      , SUM(output.value)


5. Cross validate number of addresses which own inputs
    i) BQ
    SELECT 
      COUNT(DISTINCT address)
    FROM 
      `bigquery-public-data.crypto_bitcoin.inputs` AS inputs
    CROSS JOIN  
      UNNEST(inputs.addresses) AS address
    WHERE 
     inputs.block_number <= 300

    ii) Neo4j
    MATCH n=(:Transaction)<-[:sent]-(output:Output)-[:owned]->(address:Address)
    RETURN 
        COUNT(DISTINCT address)

6. Cross validate number of addresses which own outputs 
    i) BQ
    SELECT 
      COUNT(DISTINCT address)
    FROM 
      `bigquery-public-data.crypto_bitcoin.outputs` AS outputs
    CROSS JOIN  
      UNNEST(outputs.addresses) AS address
    WHERE 
     outputs.block_number <= 300


    ii) Neo4j
    MATCH n=(:Transaction)-[:received]->(output:Output)-[:owned]->(address:Address)
    RETURN 
        COUNT(DISTINCT address)     

7. Counting Nulls
    i) Neo4j

    # Blocks
    MATCH (b:Block) 
    WHERE
        b.hash IS null OR
        b.size IS null OR
        b.stripped_size IS null OR
        b.weight IS null OR
        b.version IS null OR
        b.merkle_root IS null OR
        b.timestamp IS null OR
        b.nonce IS null OR
        b.bits IS null OR
        b.transaction_count IS null OR
        b.coinbase_param IS null
    RETURN COUNT(b)

    #Transactions
    MATCH (t:Transaction)
    WHERE
        t.hash IS null or
        t.size IS null or
        t.virtual_size IS null or
        t.version IS null or
        t.lock_time IS null or
        t.is_coinbase IS null or
        t.input_count IS null or
        t.output_count IS null
    RETURN COUNT(t)

    #Outputs
    MATCH (o:Output)<-[:received]-(:Transaction)
    WHERE
        o.required_signatures IS null or
        o.type IS null or
        o.value IS null or
        o.is_spent IS null or
        o.tx_hash IS null or
        o.output_index IS null
    RETURN COUNT(o)

    #Inputs
    MATCH (o:Output)-[:sent]->(:Transaction)
    WHERE
        o.required_signatures IS null or
        o.type IS null or
        o.value IS null or
        o.is_spent IS null or
        o.tx_hash IS null or
        o.output_index IS null or
        o.input_index IS null or
        o.is_spent IS null or
        o.spending_tx_hash IS null
    RETURN COUNT(o)

    #Address
    MATCH (a:Address)
    WHERE
        a.address_string IS null
    RETURN COUNT(a)

8. Counting Orphans
    -- i) Neo4j

    -- #Blocks
    -- MATCH links=(__b:Block)-[:next]->(_b:Block)
    -- WITH COUNT(links) as num_links
    -- MATCH (b:Block)
    -- RETURN COUNT(b) as num_blocks, COUNT(DISTINCT b.hash) as num_unique_blocks, num_links
    -- # num_links = num_unique_blocks + 1

    -- #Transactions
    -- MATCH links=(_t:Transaction)-[:at]->(:Block)
    -- WITH COUNT(links) as num_txns_linked_to_blocks
    -- MATCH (t:Transaction)
    -- RETURN COUNT(t) as num_txns, COUNT(DISTINCT t.hash) as num_unique_txns, num_txns_linked_to_blocks

    -- #Inputs without Outputs
    -- MATCH links=-(:Transaction)-[:received]->(o:Output)-[:sent]->(:Transaction)
    -- WITH COUNT(links) as num_spent_output

    -- #Address
    -- MATCH links=(_a:Address)<-[:owned]-(:Output)
    -- WITH COUNT(links) as num_address_links
    -- MATCH (a:Address)
    -- RETURN COUNT(a) as num_address, COUNT(DISTINCT a.address_string) as num_unique_address, num_address_links    


9. Duplicates

    #Blocks
    MATCH (b:Block)
    WITH b.hash as block_hash, count(*) as cnt 
    WHERE cnt > 1 
    RETURN block_hash, cnt    

    #Transactions
    MATCH (t:Transaction)
    WITH t.hash as tx_hash, count(*) as cnt 
    WHERE cnt > 1 
    RETURN tx_hash, cnt

    #Outputs
    MATCH (o:Output)
    WITH o.tx_hash as tx_hash, o.output_index as output_index, count(*) as cnt 
    WHERE cnt > 1 
    RETURN tx_hash, output_index, cnt

    #Addresses
    MATCH (a:Address)
    WITH a.address_string as address_string, count(*) as cnt
    WHERE cnt > 1
    RETURN address_string, cnt