queries.yaml

bitcoin:
    WITH double_entry_book AS (
        SELECT array_to_string(inputs.addresses, ",") as address, inputs.type, -inputs.value as value
        FROM `bigquery-public-data.crypto_bitcoin.inputs` as inputs
        WHERE block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT array_to_string(outputs.addresses, ",") as address, outputs.type, outputs.value as value
        FROM `bigquery-public-data.crypto_bitcoin.outputs` as outputs
        WHERE block_timestamp < "{{timestamp}}"
    )
    SELECT address, type, sum(value) as balance
    FROM double_entry_book
    GROUP BY 1,2
    HAVING balance > 0
    ORDER BY balance DESC

bitcoin_cash:
    WITH double_entry_book AS (
        SELECT array_to_string(inputs.addresses, ",") as address, inputs.type, -inputs.value as value
        FROM `bigquery-public-data.crypto_bitcoin_cash.inputs` as inputs
        WHERE block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT array_to_string(outputs.addresses, ",") as address, outputs.type, outputs.value as value
        FROM `bigquery-public-data.crypto_bitcoin_cash.outputs` as outputs
        WHERE block_timestamp < "{{timestamp}}"
    )
    SELECT address, type, sum(value) as balance
    FROM double_entry_book
    GROUP BY 1,2
    HAVING balance > 0
    ORDER BY balance DESC

cardano:
    WITH
    blocks AS (
          SELECT
          slot_no AS block_number,
          block_time
          FROM `blockchain-analytics-392322.cardano_mainnet.block`
          WHERE block_time < "{{timestamp}}"
    ),
    addr_outputs AS (
        SELECT
            epoch_no,
            address,
            CAST(JSON_VALUE(o, '$.idx') AS INT64) AS idx,
            CAST(JSON_VALUE(o, '$.slot_no') AS INT64) AS slot_no,
            CAST(JSON_VALUE(o, '$.txidx') AS INT64) AS txidx
        FROM
            `blockchain-analytics-392322.cardano_mainnet.rel_addr_txout`
            LEFT JOIN UNNEST(JSON_EXTRACT_ARRAY(outputs, "$") ) AS o
            JOIN blocks ON CAST(JSON_VALUE(o, '$.slot_no') AS INT64) = blocks.block_number),
    INCOMING_UTXOS AS (
        SELECT
        tio.epoch_no,
        addr_outputs.address,
        tio.slot_no,
        tio.txidx,
        addr_outputs.idx,
        CAST(JSON_VALUE(a, '$.out_value') AS INT64 ) AS value,
        FROM
        `blockchain-analytics-392322.cardano_mainnet.tx_in_out` AS tio
        JOIN
        addr_outputs
        ON
        tio.epoch_no = addr_outputs.epoch_no
        AND tio.slot_no = addr_outputs.slot_no
        AND tio.txidx = addr_outputs.txidx
        JOIN UNNEST(JSON_QUERY_ARRAY(outputs)) AS a ON CAST(JSON_VALUE(a, '$.out_idx') AS INT64 ) = addr_outputs.idx
    ),
    OUTGOING_UTXOS AS (
        SELECT 
            tx.epoch_no,
            io.address,
            consumed_in_slot_no,
            consumed_in_txidx,
            cons.index,
            value*-1 AS value,
        FROM INCOMING_UTXOS AS io 
        JOIN `blockchain-analytics-392322.cardano_mainnet.tx_consumed_output` AS cons 
            ON io.slot_no = cons.slot_no AND io.txidx = cons.txidx AND io.idx = cons.index
        JOIN blockchain-analytics-392322.cardano_mainnet.tx ON tx.slot_no = cons.consumed_in_slot_no AND tx.txidx = cons.consumed_in_txidx
        JOIN blocks ON io.slot_no = blocks.block_number
    ),
    GROUPED_INCOMING AS (
        SELECT address, COUNT(*) as count_incoming, SUM(value) as sum_incoming FROM INCOMING_UTXOS GROUP BY address
    ),
    GROUPED_OUTGOING AS (
        SELECT address, COUNT(*) as count_outgoing, SUM(value) as sum_outgoing FROM OUTGOING_UTXOS GROUP BY address
    )
    SELECT i.address, (i.sum_incoming + o.sum_outgoing) as balance
    FROM GROUPED_INCOMING AS i 
    FULL JOIN GROUPED_OUTGOING AS o ON i.address = o.address 
    WHERE (i.sum_incoming + o.sum_outgoing) > 0
    ORDER BY balance DESC

dash:
    WITH double_entry_book AS (
        SELECT array_to_string(inputs.addresses, ",") as address, inputs.type, -inputs.value as value
        FROM `bigquery-public-data.crypto_dash.inputs` as inputs
        WHERE block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT array_to_string(outputs.addresses, ",") as address, outputs.type, outputs.value as value
        FROM `bigquery-public-data.crypto_dash.outputs` as outputs
        WHERE block_timestamp < "{{timestamp}}"
    )
    SELECT address,   type,   sum(value) as balance
    FROM double_entry_book
    GROUP BY 1,2
    HAVING balance > 0
    ORDER BY balance DESC

dogecoin:
    WITH double_entry_book AS (
        SELECT array_to_string(inputs.addresses, ",") as address, inputs.type, -inputs.value as value
        FROM `bigquery-public-data.crypto_dogecoin.inputs` as inputs
        WHERE block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT array_to_string(outputs.addresses, ",") as address, outputs.type, outputs.value as value
        FROM `bigquery-public-data.crypto_dogecoin.outputs` as outputs
        WHERE block_timestamp < "{{timestamp}}"
    )
    SELECT address,   type,   sum(value) as balance
    FROM double_entry_book
    GROUP BY 1,2
    HAVING balance > 0
    ORDER BY balance DESC

ethereum:
    WITH double_entry_book AS (
        SELECT to_address as address, value AS value
        FROM `bigquery-public-data.crypto_ethereum.traces`
        WHERE to_address IS NOT null
        AND status = 1
        AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS null)
        AND block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT from_address as address, -value AS value
        FROM `bigquery-public-data.crypto_ethereum.traces`
        WHERE from_address IS NOT null
        AND status = 1
        AND (call_type NOT IN ('delegatecall', 'callcode', 'staticcall') OR call_type IS null)
        AND block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT miner AS address, sum(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) AS value
        FROM `bigquery-public-data.crypto_ethereum.transactions` AS transactions
        JOIN `bigquery-public-data.crypto_ethereum.blocks` AS blocks on blocks.number = transactions.block_number
        WHERE transactions.block_timestamp < "{{timestamp}}"
        GROUP BY blocks.miner
        UNION ALL
        SELECT from_address AS address, -(cast(receipt_gas_used as numeric) * cast(gas_price as numeric)) AS value
        FROM `bigquery-public-data.crypto_ethereum.transactions`
        WHERE block_timestamp < "{{timestamp}}"
    )
    SELECT address, sum(value) AS balance
    FROM double_entry_book
    GROUP BY address
    HAVING balance > 0
    ORDER BY balance DESC

litecoin:
    WITH double_entry_book AS (
        SELECT array_to_string(inputs.addresses, ",") as address, inputs.type, -inputs.value as value
        FROM `bigquery-public-data.crypto_litecoin.inputs` as inputs
        WHERE block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT array_to_string(outputs.addresses, ",") as address, outputs.type, outputs.value as value
        FROM `bigquery-public-data.crypto_litecoin.outputs` as outputs
        WHERE block_timestamp < "{{timestamp}}"
    )
    SELECT address,   type,   sum(value) as balance
    FROM double_entry_book
    GROUP BY 1,2
    HAVING balance > 0
    ORDER BY balance DESC

tezos:
    WITH double_entry_book as (
        SELECT IF(kind = 'contract', contract, delegate) AS address, change AS value
        FROM `public-data-finance.crypto_tezos.balance_updates`
        WHERE (status IS NULL OR status = 'applied') AND (timestamp < "{{timestamp}}")
        UNION ALL
        SELECT address, balance_change
        FROM `public-data-finance.crypto_tezos.migrations`
        WHERE timestamp < "{{timestamp}}"
    )
    SELECT address, SUM(value) AS balance
    FROM double_entry_book
    GROUP BY address
    HAVING balance > 0
    ORDER BY balance DESC

zcash:
    WITH double_entry_book AS (
        SELECT array_to_string(inputs.addresses, ",") as address, inputs.type, -inputs.value as value
        FROM `bigquery-public-data.crypto_zcash.inputs` as inputs
        WHERE block_timestamp < "{{timestamp}}"
        UNION ALL
        SELECT array_to_string(outputs.addresses, ",") as address, outputs.type, outputs.value as value
        FROM `bigquery-public-data.crypto_zcash.outputs` as outputs
        WHERE block_timestamp < "{{timestamp}}"
    )
    SELECT address,   type,   sum(value) as balance
    FROM double_entry_book
    GROUP BY 1,2
    HAVING balance > 0
    ORDER BY balance DESC